删除表中重复记录大于两条保存两条

程序员文章站 2024-01-04 09:12:52

...

delete from t_Cht_clm_reg F where F.case_id in (
select T.case_id from (
select B.* from (select e.* , row_number() over (partition by e.notice_id order by e.case_id desc) rn
from t_Cht_clm_reg e) B where B.notice_id in(
select A.notice_id from (select e.* , row_number() over (partition by e.notice_id order by e.case_id desc) rn
from t_Cht_clm_reg e) A group by A.notice_id having count(A.notice_id) > 2 )
) T where T.RN > 2 )

delete from t_Cht_clm_reg F where F.case_id in (
select T.case_id from (
select  B.* from (select e.* , row_number() over (partition by e.notice_id order by e.case_id desc) rn   
                from t_Cht_clm_reg e) B where B.notice_id in(
select  A.notice_id  from  (select e.* , row_number() over (partition by e.notice_id order by e.case_id desc) rn   
                from t_Cht_clm_reg e) A group by A.notice_id having count(A.notice_id) > 2 )
 ) T where T.RN > 2 )

相关标签：删除表中重复记录大于两条保存 delete fr