[适用版本]

通用

[解决方案]

问题:批量删除个人加班额度重复数据的参考脚本

处理方案:

--查询重复数据

select b.fnumber,b.fname_l2,a. fdatevalue,a.fproposerid,a.FWARNVALUE ,a.FLIMITVALUE,a. FADMINORGUNITID,a.FPOSITIONID

from t_ats_atspersonotlimit a

inner join t_bd_person b on a.fproposerid=b.fid

group by b.fnumber,b.fname_l2,a.fdatevalue,a.fproposerid,a.FWARNVALUE ,a.FLIMITVALUE, a.FADMINORGUNITID,a.FPOSITIONID

having count(1)>1

--备份数据

select * into atspersonotlimit0730 from t_ats_atspersonotlimit

--修复数据(员工、月份、管控额度、组织、职位完全一致的记录,只保留一条)

delete from t_ats_atspersonotlimit

where fproposerid in(select fproposerid from t_ats_atspersonotlimit

group by fdatevalue,fproposerid,FWARNVALUE ,FLIMITVALUE,fused, FADMINORGUNITID,FPOSITIONID

having count(1)>1)

and fid not in(select min(fid) from t_ats_atspersonotlimit

group by fdatevalue,fproposerid,FWARNVALUE ,FLIMITVALUE, fused,FADMINORGUNITID,FPOSITIONID

having count(1)>1