select * from tableA as T where exists(select * from tableA where stuff(Col,1,1,'')=stuff(t.Col,1,1,'') and Col<>a.Col)
--比如查找后面是201001的表的数据 declare @sql varchar(8000) set @sql='' select @sql=@sql+' select * from '+name+' union all ' from sys.tables where name like'%201001' set @sql=left(@sql,len(@sql)-10) exec(@sql)
或SELECT b.* FROM (select stuff(Col,1,1,'') AS NewCol from tableA GROUP BY stuff(Col,1,1,'') HAVING COUNT(1)>1) AS a INNER JOIN tableA AS b ON b.Col like '_'+a.NewCol
tableA--改為 sysobjects+條件Xtyp='U'/sys.tables系統表
--> 数据库版本: --> Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 --> 测试数据:[TB] IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[TB]') AND type in (N'U')) DROP TABLE [TB] GO---->建表 create table [TB]([MM] varchar(7)) insert [TB] select 'y201001' union all select 'y201002' union all select 'y201003' union all select 'm201001' union all select 'm201002' union all select 'm201004' GO--> 查询结果 SELECT * FROM [TB] A WHERE exists (SELECT MM FROM TB WHERE RIGHT(MM,LEN(MM)-1) = RIGHT(A.MM,LEN(A.MM)-1) AND MM<>A.MM) --> 删除表格 --DROP TABLE [TB]
] 表是创建好了的,这个MM 跟TB是什么啊
USE tempdb go CREATE TABLE M1000(ID1 INT) CREATE TABLE Y1000(ID1 INT) CREATE TABLE Y100Y(ID1 INT) goSELECT STUFF(Name,1,1,'') FROM sysobjects AS a WHERE xtype='U' AND Name LIKE '[MY]%' AND STUFF(Name,1,1,'') NOT LIKE '%[^0-9]%' GROUP BY STUFF(Name,1,1,'') HAVING COUNT(1)>1
SELECT NAME FROM sysobjects WHERE xtype='U' AND right(name,len(name)-1) IN ( SELECT right(name,len(name)-1) FROM sysobjects WHERE xtype='U' GROUP BY right(name,len(name)-1) HAVING COUNT(1)>1 )
declare @sql varchar(8000)
set @sql=''
select @sql=@sql+' select * from '+name+' union all '
from sys.tables
where name like'%201001'
set @sql=left(@sql,len(@sql)-10)
exec(@sql)
b.*
FROM
(select stuff(Col,1,1,'') AS NewCol from tableA GROUP BY stuff(Col,1,1,'') HAVING COUNT(1)>1) AS a
INNER JOIN tableA AS b ON b.Col like '_'+a.NewCol
--> 数据库版本:
--> Microsoft SQL Server 2008 (RTM) - 10.0.1600.22
--> 测试数据:[TB]
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[TB]')
AND type in (N'U'))
DROP TABLE [TB]
GO---->建表
create table [TB]([MM] varchar(7))
insert [TB]
select 'y201001' union all
select 'y201002' union all
select 'y201003' union all
select 'm201001' union all
select 'm201002' union all
select 'm201004'
GO--> 查询结果
SELECT *
FROM [TB] A WHERE exists (SELECT MM FROM TB WHERE RIGHT(MM,LEN(MM)-1) = RIGHT(A.MM,LEN(A.MM)-1) AND MM<>A.MM)
--> 删除表格
--DROP TABLE [TB]
表是创建好了的,这个MM 跟TB是什么啊
go
CREATE TABLE M1000(ID1 INT)
CREATE TABLE Y1000(ID1 INT)
CREATE TABLE Y100Y(ID1 INT)
goSELECT
STUFF(Name,1,1,'')
FROM sysobjects AS a
WHERE xtype='U' AND Name LIKE '[MY]%' AND STUFF(Name,1,1,'') NOT LIKE '%[^0-9]%'
GROUP BY STUFF(Name,1,1,'')
HAVING COUNT(1)>1
SELECT NAME FROM sysobjects WHERE xtype='U' AND right(name,len(name)-1)
IN
(
SELECT right(name,len(name)-1) FROM sysobjects WHERE xtype='U' GROUP BY right(name,len(name)-1)
HAVING COUNT(1)>1
)