从表cfg_ag_iad中删除字段agip,agid,relationtype重复的记录,保留updatetime为最新的一条记录。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 not in (select max(updatetime) from cfg_ag_iad group by agip,agid having count(*) >1)
有木有问题
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) 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
select agip,agid,relationtype,updatetime from
(select agip,
agid,
relationtype,
updatetime,
row_number()over(partition by agip,agid,relationtype order by updatetime desc) rn
from cfg_ag_iad)
where rn>1
试试这个吧,可以滴
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), 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), agip, agid, relationtype
from cfg_ag_iad
group by agip, agid, relationtype
having count(*) > 1)
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)
with cte as(
select row_number() over (partition by agip,agid,relationtype order by updatetime desc rid,* from tb)
)
delete from cte where rid<>1