MYSQL 从两个表计算金额？

Question

我有 2 个 mysql 表:

“订单”表:

customer_id | money
3             120
5             80
3             45
3             70
6             20

“收集”表:

customer_id | money
3             50
3             70
4             20
4             90

我想要这样的结果:

“合计”表:

customer_id | Amount
3             115
4             110
5             80
6             20

1. “总”表“customer_id”应该是单数
2. 金额 = (SUM(所有客户订单.money) - SUM(所有客户收款.money))
3. “金钱”可以为NULL

“Orders”表可以有customer_id而“Collecting”表不能有

或者

“收集”表可以有 customer_id 而“订单”表不能有

如何为输出“Total”表编写单个查询？

Answer 1

以下返回您期望的结果。

SELECT 
customer_id,
SUM(amount) as amount
FROM (
  SELECT customer_id, SUM(money) as amount
  FROM orders GROUP BY customer_id

  UNION ALL

  SELECT customer_id, SUM(money) * -1 as amount
  FROM collecting GROUP BY customer_id
) as tb
GROUP BY customer_id;

customer_id = 4 返回 -110，而不是 110，因为它只在收集表中。

示例:http://www.sqlfiddle.com/#!2/3b922/5/0