有个表,结构如: a b c
dfgh 233 1.6
dfgh 234 3.0
pole 233 5.1
pole 233 2.3
.... ... ...
把表中a相同且b相同的重复记录查询出来,如 pole 233 5.1 pole 233 2.3
dfgh 233 1.6
dfgh 234 3.0
pole 233 5.1
pole 233 2.3
.... ... ...
把表中a相同且b相同的重复记录查询出来,如 pole 233 5.1 pole 233 2.3
*
from
tb t
where
(select count(1) from tb where a=t.a and b=t.b)>1
where a.a=b.a and a.b=b.b
from tb k
where exists (select 1 from tb where a=k.a and b=k.b group by a,b having count(1)>1)
from tb k
where exists (select 1 from tb where a=k.a and b=k.b and k.c<>c)
select DISTINCT [本厂编号],[参数值],[参数描述] from [镀金板电镀面积] a where (select count(1) from [镀金板电镀面积] where [本厂编号]=a.[本厂编号] and [参数值]=a.[参数值])>1应该这样写, 试试~
(select a,b from tb group by a,b having count(*)>1)b
where a.a=b.a and a.b=b.b
insert tbtest
select 'dfgh' , 233 , 1.6 union
select 'dfgh' , 234 , 3.0 union
select 'pole' , 233 , 5.1 union
select 'pole' , 233 , 2.3--drop table tbtestSELECT * FROM tbtest T WHERE EXISTS(SELECT 1 FROM tbtest WHERE A=T.A AND B=T.B AND C<>T.C)
a b c
---------- ----------- --------------------
pole 233 2.30
pole 233 5.10(所影响的行数为 2 行)
select DISTINCT a.[本厂编号],a.[参数值],a.[参数描述] from [镀金板电镀面积$]
你的那个表名 as a where (select count(1) from a where [本厂编号]=a.[本厂编号] and [参数值]=a.[参数值])>1
where a.a=b.a and a.b=b.b
部分结果22I23GP 233 4.26
22I23GP 234 2.92
32I23GP1 233 3.83
32I23GP1 233 4.26
32I23GP1 234 2.5
32I23GP1 234 2.92 32J4HAP 233 3.94
32J4HAP 234 8.94
我想要的是红色部分的数据,怎么连其他的那些数据也查询出来了?
select * from tb a,(select a,b from tb group by a,b having count(*)>1)b
where a.a=b.a and a.b=b.b中的select a,b from tb group by a,b having count(*)>1它是得出a字段或b字段重复的记录,还是得出一个结果集,该集中的记录的a与b与下一条记录中的a与b分别相等/????
select distinct m.* from t_table m join t_table n on m.id!=n.id and m.a=n.a and m.b=n.b
这样就OK了!
where (select count(*) from 表 where a=a.a and b=a.b)>1
你的语句有漏洞的
如果数据是
a b c
dfgh 233,1.6
pole 233,1.3
pole 233,2.3
你的语句不该考虑C字段,楼主也没说C字段作为条件