mysql - 内部连接和左连接无法在mysql中按预期方式工作

我有三张桌子

table_supplier_bills - bill_id, supplier_id, date
table_supplier_bill_details - bill_id, product_id, quantity, rate
table_supplier_bill_payment_details - id, bill_id, payment_date, amount

我想把所有的帐单连同帐单金额和已付金额一起拿过来。
这是我的问题。

select
SB.bill_id,
SB.date, SB.supplier_id,
SUM(SBD.quantity * SBD.rate) as bill_amount,
COALESCE(SUM(SBPD.payment_amount), 0.00) as paid_amount
from table_supplier_bills SB
INNER JOIN
table_supplier_bill_details SBD
ON SB.bill_id = SBD.bill_id
LEFT JOIN table_supplier_bill_payment_details SBPD
ON SBD.bill_id = SBPD.bill_id
group by SBD.bill_id;

但如果一张账单的“供应商账单明细”表中有多行，则此查询不会给出正确的已付金额。如果有多行，则查询给出付款金额乘以该表的供应商账单详细信息中的行数。
有人能帮我什么忙吗？

最佳答案

请改用相关查询：

SELECT SB.bill_id,
       SB.date,
       SB.supplier_id,
       SUM(SBD.quantity * SBD.rate) as bill_amount,
       COALESCE((SELECT SUM(SBPD.payment_amount)
                 FROM table_supplier_bill_payment_details SBPD
                 WHERE SBD.bill_id = SBPD.bill_id ),0.00) as paid_amount
FROM table_supplier_bills SB
INNER JOIN table_supplier_bill_details SBD
 ON SB.bill_id = SBD.bill_id
GROUP BY SBD.bill_id;