删除重复记录解决办法

删除重复记录
从表cfg_ag_iad中删除字段agip,agid,relationtype重复的记录，保留updatetime为最新的一条记录。

SQL code

select * from cfg_ag_iad awhere (a.agip,a.agid,a.relationtype) in (select agip,agid,relationtype from cfg_ag_iad group by agip,agid,relationtype having count(*)>1)and updatetime not in (select max(updatetime) from cfg_ag_iad group by agip,agid having count(*) >1)

有木有问题

[解决办法]
and updatetime not in (select max(updatetime) from cfg_ag_iad group by agip,agid having count(*) >1)
这边会有问题吧，
比如 1,2,3, 2011/12/31 09:00:01 --1
1,2,3, 2011/12/31 09:00:02 --2
1,2,3, 2011/12/31 09:00:03 --3
1,2,4, 2011/12/31 09:00:03 --4
1,2,4, 2011/12/31 09:00:04 --5
要删除的是 --1 --2 --4
实际删除的是--1 --2
[解决办法]
试试这个……

SQL code

select agip,agid,relationtype,updatetime from (select agip,agid,relationtype,updatetime,row_number()over(partition by agip,agid,relationtype order by updatetime desc) rnfrom cfg_ag_iad)where rn>1
[解决办法]
SQL code
试试这个吧，可以滴delete from --select * from cfg_ag_iad t where (agip,agid,relationtype,updatetime) not in (  select a.agip,a.agid,a.relationtype,max(updatetime) updatetime  from cfg_ag_iad a   group by a.agip,a.agid,a.relationtype)
[解决办法]
--查询出你要的数据
select *
 from cfg_ag_iad a
  where (a.agip, a.agid, a.relationtype) in
      (select agip, agid, relationtype
         from cfg_ag_iad
        group by agip, agid, relationtype
       having count(*) > 1)
  and updatetime in (select max(updatetime) as updatetime, agip, agid, relationtype
                           from cfg_ag_iad
                          group by agip, agid, relationtype
                         having count(*) > 1)
                        
--在数据库保留你要的数据                         
delete from from cfg_ag_iad a
  where (a.agip, a.agid, a.relationtype) in
      (select agip, agid, relationtype
         from cfg_ag_iad
        group by agip, agid, relationtype
       having count(*) > 1)
  and updatetime not in (select max(updatetime) as updatetime, agip, agid, relationtype
                           from cfg_ag_iad
                          group by agip, agid, relationtype
                         having count(*) > 1)
 

 

查看更多 下一篇