求一条关于查身份证的SQL语句

[faa171544]

表字段有id name cardid。查询出同一身份证号码有两条以上记录的身份证号码及其个数。
谢谢各位大虾先~！

[zhaoxiaoming1999]

select count(*),cartid from abc group by cartid having count(*)>2;

[cosio]

with temp as
(
     select 1 a,2 b,3 c  from dual
     union all
     select 1,2,3 from dual
     union all 
     select 1,3,3 from dual
union  all
     select 1,3,3 from dual
union  all
     select 1,3,3 from dual
union  all
     select 1,5,3 from dual
union  all
     select 1,6,3 from dual
union  all
     select 1,7,3 from dual

)
select a,b,c,rn from 
(
       select a,b,c,count(1) rn from temp a  group by a ,b,c having count(1)>1
) b
group by a,b,c

[FuYongHao]

[Quote=引用 1 楼 zhaoxiaoming1999 的回复:]
select count(*),cartid from abc group by cartid having count(*)>2;
[/Quote]

UP

[oraclemch]

[Quote=引用楼主 faa171544 的回复:]
表字段有id name cardid。查询出同一身份证号码有两条以上记录的身份证号码及其个数。
  谢谢各位大虾先~！

[/Quote]

select cartid,count(cartid) from tb group by cartid having count(cartid)>2

[liusong_china]

SELECT cardid,COUNT(1) FROM tablename GROUP BY cardid HAVING COUNT(1) > 1;

应该是2条及两条以上吧

[duqiangcise]

1楼正解！

[taksunm]

[Quote=引用 5 楼 liusong_china 的回复:]
SQL codeSELECT cardid,COUNT(1)FROM tablenameGROUPBY cardidHAVINGCOUNT(1)>1;应该是2条及两条以上吧
[/Quote]
up

[ruild88]

[Quote=引用 1 楼 zhaoxiaoming1999 的回复:]
select count(*),cartid from abc group by cartid having count(*)>2;
[/Quote]
这个sql在数据量大的情况不够优化,较消耗资源

[xiangyu800]

SELECT cardid,COUNT(1) FROM tablename GROUP BY cardid HAVING COUNT(1) > 1

up