按编号进行重复记录查询

（一）查询重复记录

数据库表shiyan003记录如下：

 

（1）查询重复记录并统计重复次数

select sfzhm,count(*)重复次数 from shiyan003 group by sfzhm having count(*)>1

（2）--查询重复记录明细

select * from shiyan003

where sfzhm in (select sfzhm  from  shiyan003  group  by  sfzhm  having  count(sfzhm) > 1)

 

（二）查不重复记录

（1）保留最大行号

select * from shiyan003 where grbh in(select max(grbh) from shiyan003 group by sfzhm)

（2）保留最小行号

select * from shiyan003 where grbh in(select min(grbh) from shiyan003 group by sfzhm)