MySQL子查询关联item、stock表查询库存结果异常问题排查

你的语句存在三处核心问题，直接导致返回结果不符合预期：

• 子查询分组逻辑不合法：子查询仅按item_id分组，却同时返回qty_type字段。在MySQL非严格模式下，该写法会随机取每个item_id下任意一条记录的qty_type值，根本不会单独聚合qty_type='a'的库存。比如item_id=1同时存在v、a两类库存，分组后qty_type可能随机返回v，后续过滤时这类商品会被直接排除。
• WHERE条件抵消了LEFT JOIN作用：你将s.qty_type = 'a'写在主查询WHERE层，LEFT JOIN匹配不到右表记录时，s表所有字段值为NULL，NULL不等于'a'，所有无a类库存、无库存记录的商品都会被过滤，LEFT JOIN直接退化成INNER JOIN，自然无法返回item表全量商品。
• 外层重复聚合导致数值错误：主查询额外写了sum(current_stock)，却没有按商品维度写GROUP BY，MySQL非严格模式下会把所有匹配记录合并成单行，计算出的库存值完全错误。

SELECT 
  i.item_id,
  i.item_name,
  i.item_code,
  i.sku,
  i.min_qty,
  i.max_qty,
  COALESCE(s.current_stock, 0) AS stock
FROM item i 
LEFT JOIN (
  SELECT 
    item_id,
    SUM(qty) AS current_stock 
  FROM stock 
  WHERE qty_type = 'a'
  GROUP BY item_id
) s USING(item_id);

• 将qty_type='a'的过滤条件下推到子查询内部，先筛出可用库存记录，再按item_id聚合计算单商品库存，从根源避免qty_type取值混乱
• 过滤条件放在子查询中不会影响LEFT JOIN的返回逻辑，所有item表商品都会正常展示，无对应a类库存的商品库存值显示为0
• 去掉外层多余的SUM聚合，直接取子查询预计算好的单商品库存值，结果准确。按你给出的stock样例数据，各商品a类库存计算结果为：item_id=1为27、item_id=263为61、item_id=264为6、item_id=301为19，其余无对应a类库存的商品库存为0。

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