SQL如何从同一订单表计算多用户卖出总金额减买入总金额差值

解决方案 #

完全可以通过一条SQL实现，用条件聚合的写法即可，不需要执行两次查询再额外做逻辑计算：

SELECT 
  user,
  SUM(CASE WHEN type = 'sell' THEN amount ELSE 0 END) - SUM(CASE WHEN type = 'buy' THEN amount ELSE 0 END) AS difference
FROM sample_order
GROUP BY user;

执行逻辑说明 #

• 遍历每个用户的所有订单，用CASE条件判断订单类型：
  • 如果是卖出订单，就累加对应金额到卖出总额，否则该笔订单贡献0
  • 如果是买入订单，就累加对应金额到买入总额，否则该笔订单贡献0
• 按user字段分组后直接计算两者的差值，就能直接得到你要的difference字段

该写法仅需要扫描一次表，执行效率更高，同时自动兼容部分用户只有买入记录、或者只有卖出记录的场景，不会出现空值报错问题，执行结果与你预期的完全一致：

如果你更习惯用子查询联表的写法，也可以用下面的方式，效果一致：

SELECT 
  b.user,
  IFNULL(s.sell_amount, 0) - IFNULL(b.buy_amount, 0) AS difference
FROM (SELECT user, SUM(amount) as buy_amount FROM sample_order WHERE type='buy' GROUP BY user) b
LEFT JOIN (SELECT user, SUM(amount) as sell_amount FROM sample_order WHERE type='sell' GROUP BY user) s
ON b.user = s.user;

内容的提问来源于stack exchange，提问作者Ashiq Dey