如何查询items表并计算inwards与outwards的差值作为balance列

需要查询items表，新增一列balance，值为inwards表的quantity总和减去outwards表的quantity总和（inwards - outwards）。

表结构 #

items表 #

inwards表 #

outwards表 #

尝试的代码 #

SELECT it.* ,
(
(SELECT SUM(i.quantity)
FROM inwards AS i
WHERE i.item_id = it.id)
-
(SELECT SUM(o.quantity)
FROM outwards AS o
WHERE o.item_id = it.id)

) AS balance

FROM `items` AS it ORDER BY `id` ASC;

当前得到的结果 #

期望的结果 #

解决方案 #

问题出在当商品无入库或出库记录时，SUM()函数会返回null，而null参与算术运算结果仍为null。用COALESCE()（部分数据库可用IFNULL()/NVL()）将null替换为0，即可得到正确结果：

SELECT it.*,
       COALESCE((SELECT SUM(i.quantity) FROM inwards AS i WHERE i.item_id = it.id), 0)
       - COALESCE((SELECT SUM(o.quantity) FROM outwards AS o WHERE o.item_id = it.id), 0) AS balance
FROM `items` AS it 
ORDER BY `id` ASC;

若数据量较大，更推荐左连接+聚合的写法，性能更优：

SELECT 
    it.id,
    it.name,
    COALESCE(SUM(i.quantity), 0) - COALESCE(SUM(o.quantity), 0) AS balance
FROM items it
LEFT JOIN inwards i ON it.id = i.item_id
LEFT JOIN outwards o ON it.id = o.item_id
GROUP BY it.id, it.name
ORDER BY it.id ASC;

内容的提问来源于stack exchange，提问作者Saleh Rezq