MySQL中CASE WHEN处理NULL问题排查：无关联记录时数量未设为0

嘿，我来帮你拆解这个问题～ 你的核心需求应该是让所有商品（哪怕在item_to_inv里没有对应记录的）的最终quantity都显示为0，对吧？那你的CASE WHEN语句主要有两个关键问题：

1. 只覆盖「有记录但quantity异常」的场景，管不了「无对应记录」的商品 #

你看，对于item_id=5、8、10这类在item_to_inv里完全没有行的商品，你的子查询SELECT SUM(...) FROM item_to_inv WHERE item_id = i.id GROUP BY item_id根本不会返回任何结果——因为没有匹配的行，GROUP BY之后也不会生成一行NULL，所以外层查询的quantity列会直接是NULL。

而你的CASE WHEN是嵌套在SUM内部，用来处理item_to_inv中每一行的quantity值，但当连行都没有的时候，SUM没有计算对象，CASE WHEN根本没机会触发，自然解决不了这类商品的NULL问题。

2. 逻辑可以更简洁高效 #

另外，你的CASE WHEN逻辑（把NULL或负数转成0）其实可以用更简洁的函数组合替代：GREATEST(COALESCE(quantity, 0), 0)——COALESCE先把NULL转成0，GREATEST再确保负数也变成0，效果和你的CASE WHEN完全一致，但写法更清爽。

修正后的SQL写法 #

推荐用LEFT JOIN的方式，既高效又能覆盖所有场景：

SELECT 
    i.`id`,
    COALESCE(SUM(GREATEST(COALESCE(iti.`quantity`, 0), 0)), 0) AS `quantity`
FROM `item` AS `i`
LEFT JOIN `item_to_inv` AS `iti` ON iti.`item_id` = i.`id`
GROUP BY i.`id`;

或者如果你坚持用子查询的方式，需要在子查询外层加COALESCE来处理无记录的情况：

SELECT 
    i.`id`,
    COALESCE(
        (SELECT SUM(GREATEST(COALESCE(`quantity`, 0), 0)) 
         FROM `item_to_inv` 
         WHERE `item_id` = i.`id`),
        0
    ) AS `quantity`
FROM `item` AS `i`;

这两种写法都会把item_id=5、8、10的quantity正确显示为0，同时也处理了item_to_inv中存在但quantity为NULL或负数的情况。

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