【银企平台】删除银企平台重复交易明细数据

该帖当前只介绍如何删除银企平台产生的重复交易明细数据，暂不讨论产生原因，一般情况删除重复交易明细之后再重新查询不会再重复。

1、在银企平台-工具-重复明细检测 中查询并删除重复交易明细数据（任删除一条即可，已经产生收款单关联的交易明细就删除另一条明细）

2、如果步骤1中无法查询到重复的交易明细数据（查到了就忽略该步骤），则可依次执行以下脚本完成删除重复交易明细数据

--1）、备份明细表

select * into T_EBG_DetailInfo_bk from T_EBG_DetailInfo;

select * into T_EBG_DetailInfo_History_bk from T_EBG_DetailInfo_History ;

--2）、备份需要删除的明细id

select distinct d.id as id into T_EBG_DetailInfo_id_bk from T_EBG_DetailInfo d

left join T_EBG_DetailInfo_History dh on d.balance =dh.balance

where d.credit_Amount = dh.credit_Amount and d.debit_Amount= dh.debit_Amount and d.acc_No =dh.acc_No and d.opp_Acc_No=dh.opp_Acc_No and d.trans_Time = dh.trans_Time and d.explanation = dh.explanation;

--3）、删除后面T_EBG_DetailInfo 的重复明细数据

delete from T_EBG_DetailInfo where id in ( select id from T_EBG_DetailInfo_id_bk);

--4）、删除T_EBG_DetailInfo_History 中可能重复的交易明细

--4.1、

delete from T_EBG_DetailInfo_History where id in (

select max(id) from T_EBG_DetailInfo_History where 1=1  group by bank_Version_Id,acc_No,opp_Acc_No,balance,debit_Amount,credit_Amount,trans_Time,explanation,currency having COUNT(*)>1)

--4.2、

delete from T_EBG_DetailInfo_History where id in (select max(id) from T_EBG_DetailInfo_History where (opp_Acc_No= '*' or opp_Acc_No is null or opp_Acc_No= '') group by bank_Version_Id,acc_No,balance,debit_Amount,credit_Amount,trans_Time,explanation,currency having COUNT(*)>1)