mysql - 显示未支付或未全额支付的记录

Question

我正在尝试以下查询以获取数据

表是

1个账单明细存储账单明细

bill_details(bill_no,invoice_no,invoice_date,total,cid)

2. 账单支付详细信息存储账单支付金额它可以零个或多个条目支付账单

    bill_paid_details(id,bill_no,amount,payment_mode)
   

3. 客户详情

   customer_mapping(id,fname,lname,cid)
   

如果金额未支付或未全额支付，我想显示记录。

如果未支付金额，则表示 bill_paid_details 中没有条目

我正在尝试以下查询。

select bd.invoice_no,bd.invoice_no, bd.total,sum(bpd.amount), cm.fname
from bill_details as bd
left join customer_mapping as cm
on bd.cid=cm.cid
left join bill_paid_details as bpd
on bpd.bill_no=bd.bill_no
group by bd.invoice_no
HAVING bd.total>sum(bpd.amount)
order by bd.invoice_dt

此查询正在执行但未显示任何记录。

标签： mysql

Answer 1

您可以尝试以下查询 -

SELECT BD.invoice_dt
      ,BD.invoice_no
      ,BD.total
      ,BPD.AMT
      ,C.FNAME
FROM `bill_details` BD
LEFT JOIN (SELECT bill_no, SUM(AMOUNT) AMT
           FROM `bill_paid_details`
           GROUP BY bill_no) BPD ON BD.bill_no = BPD.bill_no
JOIN client C ON C.id = BD.cid
WHERE BD.total <> BPD.AMT
OR BPD.bill_no IS NULL
ORDER BY invoice_dt;

演示。