SQL如何计算两个表的数值差值以统计各产品盈亏情况？

方案1：单轮条件聚合（更推荐，性能更高） #

不需要拆分两次子查询，直接在一次聚合里通过条件判断分别统计销售和采购金额再计算差值即可，SQL语句如下：

SELECT 
    product_name,
    SUM(CASE WHEN transaction_type = 'sell' THEN product_value ELSE 0 END) 
    - SUM(CASE WHEN transaction_type = 'buy' THEN product_value ELSE 0 END) AS profit
FROM vendor
GROUP BY product_name;

该写法天然兼容只有采购没有销售、或者只有销售没有采购的产品场景，不会出现空值计算错误的问题。

方案2：基于现有两个子查询做关联计算 #

如果你需要保留现有两个子查询的逻辑，可以用关联查询把两个结果合并后做差值，同时用COALESCE处理空值：

SELECT 
    COALESCE(s.product_name, b.product_name) AS product_name,
    COALESCE(s.sell, 0) - COALESCE(b.buy, 0) AS profit
FROM 
    (SELECT product_name, SUM(product_value) AS sell FROM vendor WHERE transaction_type = 'sell' GROUP BY product_name) s
LEFT JOIN 
    (SELECT product_name, SUM(product_value) AS buy FROM vendor WHERE transaction_type = 'buy' GROUP BY product_name) b
ON s.product_name = b.product_name
-- 兼容只有采购没有销售的产品
UNION
SELECT 
    b.product_name,
    0 - b.buy AS profit
FROM 
    (SELECT product_name, SUM(product_value) AS buy FROM vendor WHERE transaction_type = 'buy' GROUP BY product_name) b
LEFT JOIN 
    (SELECT product_name, SUM(product_value) AS sell FROM vendor WHERE transaction_type = 'sell' GROUP BY product_name) s
ON s.product_name = b.product_name
WHERE s.product_name IS NULL;

内容的提问来源于stack exchange，提问作者Aditya Naitan