mysql - 计算来自两个表的两个不同列之和的差

我有三张不同的桌子，分别是收银台、售货台和退款台
收银台有：
收银员ID/收银员姓名
销售表有：
销售ID/出纳ID/未收货
退款表有：
退款ID/收银员ID/未付款
我想显示收银员从销售和退款中收到的总额，分别通过amuntreceved和amuntreefunded列显示。会有一个mysql语法吗？谢谢！

最佳答案

SELECT c.cashier_name AS cashier,
    COALESCE( salesByCashier.totalSales, 0 ) AS sales,
    COALESCE( refundsByCashier.totalRefunded, 0 ) AS refunds,
    COALESCE( salesByCashier.totalSales, 0 ) -
      COALESCE( refundsByCashier.totalRefunded, 0  ) AS total
FROM
  cashier c
LEFT JOIN
   ( SELECT s.cashierID AS cashierID, SUM(amountReceived) AS totalSales
     FROM sales s
     GROUP BY s.cashierID ) salesByCashier
ON c.cashierID = salesByCashier.cashierID
LEFT JOIN
   ( SELECT r.cashierID AS cashierID, SUM(amountRefunded) AS totalRefunded
     FROM refunds r
     GROUP BY r.cashierID ) refundsByCashier
ON c.cashierID = refundsByCashier.cashierID

关于mysql - 计算来自两个表的两个不同列之和的差，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/8797784/