批量处理重复数据

--1.先查出有重复记录的分组；2.查出全部重复数据；3.筛选重复数据中旧数据；4.删除旧的重复数据
with x as (select qc_d_record_sample_id, infectant_id,count(1) as num 
from qc_d_record_sample_data with(nolock) 
group by qc_d_record_sample_id,infectant_id 
having count(1) > 1),
y as (select a.sample_data_id,a.qc_d_record_sample_id,a.infectant_id,a.create_time  
from qc_d_record_sample_data a with(nolock) 
inner join x on a.qc_d_record_sample_id = x.qc_d_record_sample_id and a.infectant_id = x.infectant_id),
z as (select * from (select *,row_number() over(partition by qc_d_record_sample_id,infectant_id order by create_time desc) rn
from y) s where s.rn > 1) 
delete from qc_d_record_sample_data from qc_d_record_sample_data b 
inner join z on b.sample_data_id = z.sample_data_id