解决MySQL子查询返回多行错误，按托盘统计产品可用库存

现有如下数据表：

需要统计product_id=1的产品按pallet_id分组的可用库存，计算规则为：每组中action为ADD的数量总和减去action为REMOVE的数量总和，期望结果如下：

尝试的SQL语句及问题：

1. 初始SQL：

SELECT ((SELECT sum(DISTINCT qty) as ADDING FROM table WHERE action IN ('ADD') AND  product_id IN (1)) - ifnull((SELECT sum(DISTINCT qty) as REMOVING FROM table WHERE action IN ('REMOVE') AND product_id IN (1)),0)) as avaiable_qty, pallet_id FROM table WHERE product_id IN (1) GROUP BY pallet_id ORDER BY product_id DESC

问题：所有行的可用库存均为120，因为子查询未关联当前分组的pallet_id，计算的是全表所有product_id=1的ADD和REMOVE总和。

2. 修改后的SQL：

SELECT ((SELECT sum(DISTINCT qty) as ADDING FROM table WHERE action IN ('ADD') AND  product_id IN (1) GROUP BY pallet_id) - ifnull((SELECT sum(DISTINCT qty) as REMOVING FROM table WHERE action IN ('REMOVE') AND product_id IN (1) GROUP BY pallet_id),0)) as avaiable_qty, pallet_id FROM table WHERE product_id IN (1) GROUP BY pallet_id ORDER BY product_id DESC

问题：MySQL返回错误#1242 subquery returns more than 1 row，因为子查询使用GROUP BY pallet_id后返回多行结果，主查询无法将单行结果与分组对应。

使用条件聚合可以简洁高效地实现需求，无需嵌套子查询：

SELECT
    ROW_NUMBER() OVER (ORDER BY pallet_id DESC) AS id,
    pallet_id AS `pallet_id（托盘ID）`,
    SUM(CASE WHEN action = 'ADD' THEN qty ELSE 0 END) - SUM(CASE WHEN action = 'REMOVE' THEN qty ELSE 0 END) AS `available qty（可用库存）`
FROM
    `table`
WHERE
    product_id = 1
GROUP BY
    pallet_id
ORDER BY
    pallet_id DESC;

代码解释 #

• SUM(CASE WHEN action = 'ADD' THEN qty ELSE 0 END)：计算每个pallet_id分组下所有ADD操作的数量总和
• SUM(CASE WHEN action = 'REMOVE' THEN qty ELSE 0 END)：计算每个pallet_id分组下所有REMOVE操作的数量总和
• 两者相减得到分组后的可用库存
• ROW_NUMBER() OVER (ORDER BY pallet_id DESC)：生成结果中的自增id，与期望结果的排序一致
• WHERE product_id = 1：过滤目标产品，减少不必要的计算

错误原因说明 #

1. 第一个SQL的子查询未关联pallet_id，导致每个分组都复用了全局的ADD/REMOVE总和，结果全部相同。
2. 第二个SQL的子查询返回多行（每个pallet_id对应一行），主查询无法将多行子查询结果与单个分组匹配，因此触发subquery returns more than 1 row错误。

额外注意：不要使用sum(DISTINCT qty)，如果同一分组下有相同数量的操作，DISTINCT会忽略重复值，导致计算结果错误。比如若分组内有两个ADD 100的记录，sum(DISTINCT qty)只会计算一次100，而非正确的200。

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