SELECT a.account_cd, a.invoice_type, a.invoice_date,due_date, a.inv_crd_no, a.invoice_amt, a.balance_amt,
b.pay, b.charge, b.discount, b.unapplied, (pay+charge+discount+unapplied) * (-1) sum_pay
FROM ar_invoice a,
(SELECT account_cd, inv_crd_no, SUM(pay_amt) pay, SUM(charge_amt) charge,
SUM(discount_amt) discount, SUM(unapplied_amt) unapplied
FROM ar_pay
GROUP BY account_cd, inv_crd_no) b
WHERE a.account_cd = b.account_cd
AND a.inv_crd_no = b.inv_crd_no
AND a.invoice_amt <> (pay+charge+discount+unapplied) * (-1)
AND a.balance_amt = 0
ORDER BY a.account_cd, a.invoice_date, a.inv_crd_no
a表那里的select没有必要,a表与b表应该是总表与明细表的关系,所以b表与a表关联时需要先进行聚合处理
1. 列使用了别名,比如有计算列,写在外层可能要写好几遍。
2.最开始的语句有如同 b 一样的聚合,后来有了数据结构的调整,就改成了现在这个样子,谁也不知道是原来的语句是什么了。