MySQL如何对两个不同表的列做减法计算全量用户净资产变化

你当前使用的是内连接JOIN，只会返回同时存在于sent和received两个视图中的用户，因此只有同时有转出和转入记录的用户会出现在结果中，只有转出的用户5、只有转入的用户4就被过滤掉了。

方案1：基于现有视图调整查询 #

MySQL不支持全外连接，我们可以先通过UNION拿到所有涉及的用户ID，再左关联两个视图计算净资产变化：

SELECT 
  u.user_id AS 'user',
  COALESCE(r.total_received, 0) - COALESCE(s.total_sent, 0) AS net_change
FROM (
  -- 拿到所有出现过的用户ID
  SELECT sender AS user_id FROM sent
  UNION
  SELECT receiver AS user_id FROM received
) u
LEFT JOIN sent s ON u.user_id = s.sender
LEFT JOIN received r ON u.user_id = r.receiver
ORDER BY net_change DESC;

执行后返回的完整结果如下：

+------+------------+
| user | net_change |
+------+------------+
|    1 |         20 |
|    3 |          5 |
|    4 |          5 |
|    5 |        -10 |
|    2 |        -20 |
+------+------------+

方案2：直接从原始交易表计算（更简洁高效） #

不需要预先创建统计视图，可以直接对原始交易表做行列转换计算：

SELECT 
  user_id AS 'user',
  SUM(amount) AS net_change
FROM (
  -- 转入记录金额记为正
  SELECT receiver AS user_id, amount FROM transactions1
  UNION ALL
  -- 转出记录金额记为负
  SELECT sender AS user_id, -amount FROM transactions1
) t
GROUP BY user_id
ORDER BY net_change DESC;

这个方案逻辑更直观，也避免了多表关联的性能消耗，返回结果和方案1完全一致。

内容的提问来源于stack exchange，提问作者Angel Torres