按两个字段统计重复记录

,

前4条记录的sfzhm是相同的

（1）查找xm，sfzhm不重复的记录

select distinct xm,sfzhm from shiyan003 group by xm,sfzhm

运行如下：

 

（2）以xm,sfzhm查询重复的记录

select * from shiyan003 a
where a.xm in  (select xm from shiyan003 group by xm,sfzhm  having count(*) > 1)
and a.sfzhm in  (select sfzhm from shiyan003 group by xm,sfzhm  having count(*) > 1)

 

运行如下：

（3）亦可如下查询：

select *from shiyan003 a
where exists (select 1
              from (select xm, sfzhm 
                    from shiyan003
                    group by xm, sfzhm
                    having count(*) > 1) s
             where s.xm = a.xm
             and s.sfzhm = a.sfzhm)

运行如下：

 

（4）

SELECT XM,SFZHM,COUNT(1)  FROM SHIYAN003 A  GROUP BY A.XM,A.SFZHM HAVING COUNT(1) > 1

上面的命令也可这样写：

select A.XM,A.SFZHM,COUNT(*) FROM shiyan003 a GROUP BY A.xm,A.sfzhm HAVING COUNT(*)>1