select b.A1,b.A2,b.A3 from A b where exists (select 1 from A a where a.A1 = b.A1 group by A2 having count(*) > 1)
select A.A1,A.A2,A.A3 from A, (select A2,count(*) total from a group by A2) V where A.A2=V.A2 and V.total>1;
--查询 SELECT b.A1, b.A2, b.A3 FROM A b WHERE b.A2 IN (SELECT a.A2 FROM A a WHERE a.A1 = b.A1 GROUP BY A2 HAVING COUNT(*) > 1);--如果要删除 DELETE FROM A a WHERE a.A1 != (SELECT MAX(b.A1) FROM A b WHERE b.A2 = a.A2);
select A1,A2,A3 from A where exists (select A2 from askquotelist having count(A2) > 1)
二楼suiziguo大侠的能用,一楼、三楼都查不出数据;四楼提示没有“askquotelist”视图,大概和我的oracle版本为9有关系吧,在其他地方查的,查询重复身份证还能用这句: select a1,a2,a3 from a where rowid != (select max(rowid) from a b where b.a2 = a.2 ) 也能查出来,但和二楼的结果不一致,二楼的数据多,我没有认真比对过。不知为什么。
select b.A1,b.A2,b.A3
from A b
where exists
(select 1 from A a where a.A1 = b.A1 group by A2 having count(*) > 1)
(select A2,count(*) total from a
group by A2) V
where A.A2=V.A2 and V.total>1;
SELECT b.A1, b.A2, b.A3
FROM A b
WHERE b.A2 IN (SELECT a.A2 FROM A a WHERE a.A1 = b.A1 GROUP BY A2 HAVING COUNT(*) > 1);--如果要删除
DELETE FROM A a WHERE a.A1 != (SELECT MAX(b.A1) FROM A b WHERE b.A2 = a.A2);
select a1,a2,a3
from a
where rowid != (select max(rowid)
from a b
where b.a2 = a.2 )
也能查出来,但和二楼的结果不一致,二楼的数据多,我没有认真比对过。不知为什么。
你该语句,过滤了每一身份证号的重复记录其中的一条(即rowid=(max(rowid))这一条)
所以我语句返回的数据多。
你的语句是用来删除重复记录,只保留其中一条(即上面那条)时所用的。
如果你要删除相同身份证号的重复数据,可以这样做。但你题目中要求的是查询出来,所以我如此写了,呵呵。