首先有一个字典表,里面有唯一主键id,编码drgs_code,年份year,有效性is_valid等关联条件因为有重复数据,所以导致关联时界面显示重复数据
第一次先通过编码drgs_code,年份year,有效性is_valid(1是有效的)分组查询:
select dzd.drgs_code, dzd.year, dzd.is_valid
from dw_zd_year_drgsinfo dzd
group by dzd.drgs_code, dzd.year, dzd.is_valid
having count(1) > 1 and dzd.is_valid = '1'
然后查询重复数据中最小的主键id:
select min(dzd.id)
from dw_zd_year_drgsinfo dzd
group by dzd.drgs_code, dzd.year, dzd.is_valid
having count(1) > 1 and dzd.is_valid = '1'
最后通过not in 找到重复数据里,除了最小ID的数据,就可以直接删除了
select * from dw_zd_year_drgsinfo dz
where (dz.drgs_code, dz.year, dz.is_valid) in
(select dzd.drgs_code, dzd.year, dzd.is_valid
from dw_zd_year_drgsinfo dzd
group by dzd.drgs_code, dzd.year, dzd.is_valid
having count(1) > 1 and dzd.is_valid = '1')
and dz.id not in (select min(dzd.id)
from dw_zd_year_drgsinfo dzd
group by dzd.drgs_code, dzd.year, dzd.is_valid
having count(1) > 1 and dzd.is_valid = '1')