[适用版本]

8.5sp1,8.6,8.6sp1

[解决方案]

问题:如何批量修复额度周期重复的数据

参考sql脚本:

--查出异常数据

select a.FPROPOSERID , a.fid,a.fcyclebegindate,a.fcycleenddate,a.FSTANDARDLIMIT ,a.FREALLIMIT ,a.FUSEDLIMIT ,a.FREEZELIMIT ,a.FREMAINLIMIT ,a.FCYCLETYPE

from t_hr_ats_holidaylimit a

inner join(

select FPROPOSERID ,FHOLIDAYPOLICYID,FCYCLEBEGINDATE ,FCYCLEENDDATE

from t_hr_ats_holidaylimit

where FCYCLEBEGINDATE >={ts '2020-01-01'}

group by fproposerid,FHOLIDAYPOLICYID ,FCYCLEBEGINDATE ,FCYCLEENDDATE

having count(1)>1

)b on a.fproposerid=b.fproposerid and a.fholidaypolicyid=b.fholidaypolicyid and a.fcyclebegindate =b.fcyclebegindate and a.fcycleenddate=b.fcycleenddate

order by FPROPOSERID

--备份数据

select * into holidaylimit1113 from t_hr_ats_holidaylimit

select * into holidaylimitdetail1114 from t_hr_ats_holidaylimitdetail

select a.fproposerid,b.fname_l2,b.fnumber

from holidaylimit1113 a

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

where a.fusedlimit=0 and a.fcyclebegindate>={ts '2020-01-01'}

group by a.FPROPOSERID ,a.FHOLIDAYPOLICYID,a.FCYCLEBEGINDATE ,a.FCYCLEENDDATE ,b.fname_l2,b.fnumber

having count(1)>1

9040691,2509,9020199,9040973,102463,103745,103864,9000319,9000064,3315

--修复数据

--删除已用和冻结均为0且周期重复的数据

delete from t_hr_ats_holidaylimt

where fid in(

select min(fid)

from t_hr_ats_holidaylimit

where fusedlimit=0 and FREEZELIMIT=0 and FCYCLEBEGINDATE >={ts '2020-01-01'}

group by fproposerid,FHOLIDAYPOLICYID ,FCYCLEBEGINDATE ,FCYCLEENDDATE

)

--删除周期重复,已用和冻结额度均为0的那条数据

delete from t_hr_ats_holidaylimit

where fid in(

select a.fid--,a.FSTANDARDLIMIT ,a.FREALLIMIT ,a.FUSEDLIMIT ,a.FREEZELIMIT ,a.FREMAINLIMIT ,a.FCYCLETYPE

from t_hr_ats_holidaylimit a

inner join(

select FPROPOSERID ,FHOLIDAYPOLICYID,FCYCLEBEGINDATE ,FCYCLEENDDATE

from t_hr_ats_holidaylimit

where FCYCLEBEGINDATE >={ts '2020-01-01'}

group by fproposerid,FHOLIDAYPOLICYID ,FCYCLEBEGINDATE ,FCYCLEENDDATE

having count(1)>1

)b on a.fproposerid=b.fproposerid and a.fholidaypolicyid=b.fholidaypolicyid and a.fcyclebegindate =b.fcyclebegindate and a.fcycleenddate=b.fcycleenddate

where a.fusedlimit=0 and a.FREEZELIMIT=0

)

--重复数据均被使用的额度需逐条修复

--u5QAABuIZgeoBPQd u5QAABuIZieoBPQd

update t_hr_ats_holidaylimitdetail set FHOLIDAYLIMITID ='u5QAABuIZgeoBPQd' where FHOLIDAYLIMITID ='u5QAABuIZieoBPQd' and FCHANGETYPE =4

delete from t_hr_ats_holidaylimit where fid='u5QAABuIZieoBPQd'