>我有一个客户表、订单表和付款表。
从这些中,我试图获得一个每条记录的表:客户#,订单#,到期余额。但是,如果客户没有订单,那么我只想列出客户#,其他字段的值为空。
使用 LEFT JOIN,我只能对客户#和订单#执行此操作,但我也无法弄清楚如何在那里获得到期余额。我对Mysql相当陌生,并试图搜索答案,但无法。
以下是仅适用于客户#和订单#的方法:
SELECT
customers.cust_num,
orders.order_id
FROM customers
LEFT JOIN orders ON customers.cust_num = orders.cust_num
但我正在尝试将"orders.invoice_amount - SUM(payments.amount) AS balance_due"合并为另一列,其中付款表通过两个中名为"order_id"的字段与订单表相关。
也许像这样: SELECT orders.invoice_amount - SUM(payments.amount) AS balance_due FROM payments, orders WHERE payments.order_id = orders.order_id
知道我该如何做到这一点或指向正确的方向吗?
执行额外的左联接,但对按顺序分组的付款进行子查询。 因此,如果它在预先查询的结果中找到记录,您就可以开始了。 此外,我改为使用表"别名"以缩短可读性,尤其是在表名变长的情况下,或者您必须在查询中多次联接到同一个表。
SELECT
c.cust_num,
coalesce( o.order_id, 0 ) as Order_ID,
coalesce( o.invoice_amount, 0 ) as InvoiceAmount,
coalesce( Prepaid.TotalPaid, 0 ) as TotalPaid,
coalesce( o.invoice_amount - coalesce( PrePaid.TotalPaid, 0 ), 0) as BalanceDue
FROM
customers c
LEFT JOIN orders o
ON c.cust_num = o.cust_num
LEFT JOIN
( select
p.order_id,
sum( p.amount ) as totalPaid
from
payments p
group by
p.order_id ) as PrePaid
on o.order_id = PrePaid.order_id
尝试类似的东西
SELECT
customers.cust_num,
orders.order_id,
orders.invoice_amount - SUM(payments.amount)
FROM customers
LEFT JOIN orders ON customers.cust_num = orders.cust_num
INNER JOIN payments P ON P..order_id = orders.order_id
group by cust_num,order_id,orders.invoice_amount