--去重历史价格信息
update CT_TDS_SaleDCPH set CFIsEnable = 0 where FID in(
select min(deph.fid)
from CT_TDS_SaleDCPH deph
left join CT_TDA_SaleDataCenter c on c.fid = deph.fparentid
where deph.CFIsEnable = 1
and deph.fparentid in(
select s.fid from CT_TDA_SaleDataCenter s
left join CT_BAS_MarketPlace m on m.fid=s.CFMarketID
where m.CFMarketNum in ('D-MTMCGZC') --市场编码
and to_char(C.FBizDate,'yyyy-MM-dd')>='2023-08-08' --业务日期
and to_char(C.FBizDate,'yyyy-MM-dd')<='2023-08-08'
)
group by deph.fparentid,deph.CFMATERIALID,deph.CFTimestamp
having count(deph.CFMATERIALID) > 1
);
commit;
--去重价格信息分录
delete from CT_TDS_SaleDCPE where fid in (
select min(dcpe.fid) from CT_TDS_SaleDCPE dcpe
left join CT_TDA_SaleDataCenter c on c.fid = dcpe.fparentid
left join CT_BAS_MarketPlace m on m.fid=c.CFMarketID
where m.CFMarketNum in ('D-MTMCGZC') --市场编码
and to_char(C.FBizDate,'yyyy-MM-dd')>='2023-08-08' --业务日期
and to_char(C.FBizDate,'yyyy-MM-dd')<='2023-08-08'
group by
dcpe.CFMATERIALID
having count(dcpe.CFMATERIALID) > 1
)
commit; |