MySQL中按ProductID和Price汇总(qtyin-qtyout)的查询需求

需求回顾 #

对Stock表按相同ProductID和Price分组，计算sum(QtyIn - QtyOut)作为balance；结果需包含每组最小的id，最终按ProductID、id、Price排序。

正确SQL语句 #

SELECT 
    MIN(id) AS id,
    ProductID,
    SUM(COALESCE(QtyIn, 0) - COALESCE(QtyOut, 0)) AS balance,
    Price
FROM Stock
GROUP BY ProductID, Price
ORDER BY ProductID, id, Price;

关键说明 #

1. 获取每组最小id：用MIN(id)确保结果中的id是该ProductID+Price组里最早的记录id，匹配预期结果要求。
2. 处理空值：用COALESCE函数把空的QtyIn/QtyOut转为0，避免因NULL导致计算结果异常（比如id=7的记录QtyIn为空，转0后计算0-20才正确）。
3. 分组与聚合：严格按ProductID和Price分组，通过SUM()计算每组的balance，逻辑直接清晰。
4. 排序规则：最终按ProductID、id、Price排序，满足需求中的排序要求。

原SQL问题分析 #

• 分组后直接取id属于未定义行为，MySQL在未开启ONLY_FULL_GROUP_BY时会返回随机id，无法保证是组内最小id。
• 错误混合使用用户变量和聚合函数，逻辑混乱，完全没必要用变量实现这个分组聚合需求。

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