A B C
------------------
1 1 1
1 1 2
1 1 3
1 2 1<-重复
1 2 1<-重复
1 2 2
1 2 3<-重复
2 1 1
2 1 2
1 2 3<-重复
2 2 1
2 2 2
要找出上表中重复的几条记录,请问Sql该怎么写?
5 个解决方案
#1
declare @t table(A int,B int,C int)
insert into @t select 1 ,1 ,1
union all select 1 ,1 ,2
union all select 1 ,1 ,3
union all select 1 ,2 ,1
union all select 1 ,2 ,1
union all select 1 ,2 ,2
union all select 1 ,2 ,3
union all select 2 ,1 ,1
union all select 2 ,1 ,2
union all select 1 ,2 ,3
union all select 2 ,2 ,1
union all select 2 ,2 ,2
select a,b,c from @t group by a,b,c having count(*)>1
insert into @t select 1 ,1 ,1
union all select 1 ,1 ,2
union all select 1 ,1 ,3
union all select 1 ,2 ,1
union all select 1 ,2 ,1
union all select 1 ,2 ,2
union all select 1 ,2 ,3
union all select 2 ,1 ,1
union all select 2 ,1 ,2
union all select 1 ,2 ,3
union all select 2 ,2 ,1
union all select 2 ,2 ,2
select a,b,c from @t group by a,b,c having count(*)>1
#2
能帮我找出所有重复的记录吗?如下面所示:
结果:
A B C
-------------------------
1 2 1<-重复
1 2 1<-重复
1 2 3<-重复
1 2 3<-重复
结果:
A B C
-------------------------
1 2 1<-重复
1 2 1<-重复
1 2 3<-重复
1 2 3<-重复
#3
declare @t table(A int,B int,C int)
insert into @t select 1 ,1 ,1
union all select 1 ,1 ,2
union all select 1 ,1 ,3
union all select 1 ,2 ,1
union all select 1 ,2 ,1
union all select 1 ,2 ,2
union all select 1 ,2 ,3
union all select 2 ,1 ,1
union all select 2 ,1 ,2
union all select 1 ,2 ,3
union all select 2 ,2 ,1
union all select 2 ,2 ,2
select a.* from @t a,(select a,b,c from @t group by a,b,c having count(*)>1)b where a.a=b.a and a.b=b.b and a.c=b.c
insert into @t select 1 ,1 ,1
union all select 1 ,1 ,2
union all select 1 ,1 ,3
union all select 1 ,2 ,1
union all select 1 ,2 ,1
union all select 1 ,2 ,2
union all select 1 ,2 ,3
union all select 2 ,1 ,1
union all select 2 ,1 ,2
union all select 1 ,2 ,3
union all select 2 ,2 ,1
union all select 2 ,2 ,2
select a.* from @t a,(select a,b,c from @t group by a,b,c having count(*)>1)b where a.a=b.a and a.b=b.b and a.c=b.c
#4
谢谢,问题解决了!
#5
关注
#1
declare @t table(A int,B int,C int)
insert into @t select 1 ,1 ,1
union all select 1 ,1 ,2
union all select 1 ,1 ,3
union all select 1 ,2 ,1
union all select 1 ,2 ,1
union all select 1 ,2 ,2
union all select 1 ,2 ,3
union all select 2 ,1 ,1
union all select 2 ,1 ,2
union all select 1 ,2 ,3
union all select 2 ,2 ,1
union all select 2 ,2 ,2
select a,b,c from @t group by a,b,c having count(*)>1
insert into @t select 1 ,1 ,1
union all select 1 ,1 ,2
union all select 1 ,1 ,3
union all select 1 ,2 ,1
union all select 1 ,2 ,1
union all select 1 ,2 ,2
union all select 1 ,2 ,3
union all select 2 ,1 ,1
union all select 2 ,1 ,2
union all select 1 ,2 ,3
union all select 2 ,2 ,1
union all select 2 ,2 ,2
select a,b,c from @t group by a,b,c having count(*)>1
#2
能帮我找出所有重复的记录吗?如下面所示:
结果:
A B C
-------------------------
1 2 1<-重复
1 2 1<-重复
1 2 3<-重复
1 2 3<-重复
结果:
A B C
-------------------------
1 2 1<-重复
1 2 1<-重复
1 2 3<-重复
1 2 3<-重复
#3
declare @t table(A int,B int,C int)
insert into @t select 1 ,1 ,1
union all select 1 ,1 ,2
union all select 1 ,1 ,3
union all select 1 ,2 ,1
union all select 1 ,2 ,1
union all select 1 ,2 ,2
union all select 1 ,2 ,3
union all select 2 ,1 ,1
union all select 2 ,1 ,2
union all select 1 ,2 ,3
union all select 2 ,2 ,1
union all select 2 ,2 ,2
select a.* from @t a,(select a,b,c from @t group by a,b,c having count(*)>1)b where a.a=b.a and a.b=b.b and a.c=b.c
insert into @t select 1 ,1 ,1
union all select 1 ,1 ,2
union all select 1 ,1 ,3
union all select 1 ,2 ,1
union all select 1 ,2 ,1
union all select 1 ,2 ,2
union all select 1 ,2 ,3
union all select 2 ,1 ,1
union all select 2 ,1 ,2
union all select 1 ,2 ,3
union all select 2 ,2 ,1
union all select 2 ,2 ,2
select a.* from @t a,(select a,b,c from @t group by a,b,c having count(*)>1)b where a.a=b.a and a.b=b.b and a.c=b.c
#4
谢谢,问题解决了!
#5
关注