有表:T1(ID,A,B,C):
ID A B C
1 abc def ggg
2 bc ddg sss
3 bef defg dds
4 abcd ehai jijl我想求以下Sql语句:
1、根抿A字段查重复,只要某一记录里的A字段值包含在其它记录A字段值,则认为两条记录重复.
如得结果为:
ID A B C
1 abc def ggg
2 bc ddg sss
4 abcd ehai jijl2、查重复条件同1,只是条件可以是任何字段的组合。
ID A B C
1 abc def ggg
2 bc ddg sss
3 bef defg dds
4 abcd ehai jijl我想求以下Sql语句:
1、根抿A字段查重复,只要某一记录里的A字段值包含在其它记录A字段值,则认为两条记录重复.
如得结果为:
ID A B C
1 abc def ggg
2 bc ddg sss
4 abcd ehai jijl2、查重复条件同1,只是条件可以是任何字段的组合。
insert into @t select 1 ,'abc' ,'def' ,'ggg'
union all select 2 ,'bc' ,'ddg' ,'sss'
union all select 3 ,'bef' ,'defg' ,'dds'
union all select 4 ,'abcd' ,'ehai' ,'jijl'
union all select 3 ,'bef' ,'defg' ,'dds'--1.
select * from @t where A not in(select A from @t group by A having count(*)>1)--2.
select distinct * from @t--不知道是不是楼主的意思
Where Exists(Select 1 From T1 Where CharIndex(A,A.A)>0 And ID<>A.ID)
Or Exists(Select 1 From T1 Where CharIndex(A.A,A)>0 And ID<>A.ID)
A.* From T1 A
Inner Join T1 B
On (CharIndex(A.A,B.A)>0 And A.ID<>B.ID)
Or (CharIndex(B.A,A.A)>0 And A.ID<>B.ID)
Insert T1 Select 1, 'abc', 'def', 'ggg'
Union All Select 2, 'bc', 'ddg', 'sss'
Union All Select 3, 'bef', 'defg', 'dds'
Union All Select 4, 'abcd', 'ehai', 'jijl'
GO
Select * From T1 A
Where Exists(Select 1 From T1 Where CharIndex(A,A.A)>0 And ID<>A.ID)
Or Exists(Select 1 From T1 Where CharIndex(A.A,A)>0 And ID<>A.ID)Select Distinct
A.* From T1 A
Inner Join T1 B
On (CharIndex(A.A,B.A)>0 And A.ID<>B.ID)
Or (CharIndex(B.A,A.A)>0 And A.ID<>B.ID)
Go
Drop Table T1
/*
ID A B C
1 abc def ggg
2 bc ddg sss
4 abcd ehai jijl
*/
insert into @t select 1 ,'abc' ,'def' ,'ggg'
union all select 2 ,'bc' ,'ddg' ,'sss'
union all select 3 ,'bef' ,'defg' ,'dds'
union all select 4 ,'abcd' ,'ehai' ,'jijl'
union all select 3 ,'bef' ,'defg' ,'dds'
select b.a,max(b.b),max(b.c) from @t a inner join @t b on a.A not like b.a and a.id<b.id group by b.a,b.b,b.c
paoluo(一天到晚游泳的鱼) 的结果是我想要的结果,不过还有一点:怎么将结果按重复内容排序?
xeqtr1982(Visual C# .NET)和 itblog(BegCSharp) 两位的可能没明白我的意思,我是想被包含时就算是重复,而不是要完全相等才算重复。不过还是要谢谢你们
Where Exists(Select 1 From T1 Where CharIndex(A,A.A)>0 And ID<>A.ID)
Or Exists(Select 1 From T1 Where CharIndex(A.A,A)>0 And ID<>A.ID)
OrSelect Distinct
A.* From T1 A
Inner Join T1 B
On (CharIndex(A.A,B.A)>0 And A.ID<>B.ID)
Or (CharIndex(B.A,A.A)>0 And A.ID<>B.ID)