资管云-SQL查询预收款收款单的实际转出金额原创
1人赞赏了该文章
88次浏览
编辑于2024年06月28日 17:21:53
背景:收款明细的转款金额(t_cha_receiptdetail.ftransferamt)与实际转出金额不等,需通过收款单明细与转款单明细进行关联,确定实际转出金额,后续将基于查出的数据进行数据更新。
SQL:
select * from ( select d.fid,d.fname, c.FDETAILID,c.FBILLNO, c.FACTAMOUNT, c.ftransferamt, 0 - IFNULL(sum(a.famount), 0) ftransferamtExpected from t_cha_receiptdetail c JOIN t_cha_receiptdetail_v b on c.FDETAILID = b.FDETAILID AND b.FINVOICESTATUS in ('0','10') AND c.FREVBILLTYPE IN ('gathering','changeMoneydefine') and c.FCREATETIME > '2024-01-01' and c.FMONEYDEFINETYPE = 'PREMONEY' and c.FISCANCEL <> '1' and c.FACCOUNTENTRYID > 0 and c.fbillstatus = 'C' left join t_cha_transferbill a ON a.FACCOUNTENTRYID = c.FACCOUNTENTRYID and a.FAMOUNT < 0 and a.FISDELETE <> '1' and a.FISCANCEL <> '1' and a.FBILLSTATUS <> 'D' and a.FMONEYDEFINEOUTID = c.FMONEYDEFINEID JOIN t_psmd_project_l d on d.fid = c.FPROJECTID and d.FLOCALEID = 'zh_CN' -- where 可在此增加查询条件,比如所属项目 group by c.FDETAILID) tmp WHERE tmp.ftransferamt <> tmp.ftransferamtExpected;