如何有条件的查询或删除表中的重复记录

iamlaosong文

数据库中有重复记录，需要用SQL语句删除或者提出来，无条件的删除重复记录（就是保留一条，其它都删除）可以通过rowid删除，做删除之前可以先将查询语句写好，语句如下：

select * from mytable a
where a.rowid != (select max(b.rowid) from mytable b 
where a.id=b.id and a.dsno=b.dsno); 

执行上述SQL语句后就可以显示所有id和dsno相同且重复的记录。 

现在有个差不多的需求，需求是找出邮件号码重复的记录（注意要所有字段都提出来），每个重复的邮件号码保留封发日期最大的那个，如果封发日期相同，则保留费用高的那个，这个SQL语句怎么写？

其实需求也很简单，只要将邮件号、封发日期和费用三个字段按降序排序，保留每个邮件号码的第一条记录，其它的删除就行了。我想了很多办法，找出排序后的第一行或者其它记录很简单，只需要在结果外面加个查询即可，如下面语句可以可以查询出除第一行以外的其它行记录：

select * from (select * from mytable where id='2300' order by dsno desc) where rownum != 1;

可是要查出所有id上面语句就不行了，这时我想到了Oracle的分析函数，利用它可以达到上述目的，语句如下：

select *
  from (select t.rowid,
               t.*,
               row_number() over(PARTITION BY t.yjh ORDER BY t.ffrq, t.yjfyhj desc) hh
          from emsapp_gjyj_eyb_yssj t)
 where hh != 1;

语句中的rowid是为了其它用途，可以不要。

如果要查询出所有的重复记录，语句如下：

select *
  from emsapp_gjyj_eyb_yssj t
 where t.yjh in (select a.yjh
                   from emsapp_gjyj_eyb_yssj a
                  group by a.yjh
                 having count(*) > 1);

或者

select count(*)
  from emsapp_gjyj_eyb_yssj t
 where (select count(*) from emsapp_gjyj_eyb_yssj a where a.yjh = t.yjh) > 1;

第二条语句要慢得多（每个号都要统计一次），不要也罢，记录在此只是觉得这种写法比较有趣。

参见：Oracle分析函数详解、oracle分析函数技术详解（配上开窗函数over()）