MySQL JOIN无匹配时返回空字段 按地点查询清单对应商品库存问题

核心问题根因 #

你碰到的缺失问题由两个逻辑错误导致：

• 左连接transactions表后，将transactions.organisationId = 1的过滤条件写在WHERE子句中，会把没有匹配交易记录的行直接过滤，相当于把LEFT JOIN降级为INNER JOIN
• 没有预先生成「所有地点 × 符合条件的清单商品」的全量维度组合，只有当某个地点存在对应商品的交易记录时才会生成对应行，无交易的组合自然不会返回

调整方案 #

1. 先构造全量维度组合：先取出所有需要的地点、符合过滤条件的清单+商品的笛卡尔积，确保你需要的所有行都先存在
2. 把transactions表的过滤条件移动到LEFT JOIN的ON子句中，避免过滤无交易的行
3. 用COALESCE函数把SUM结果为空的情况默认转为0，符合你优先返回0的需求

修改后查询语句 #

SELECT 
  locations.locationId as locationId,
  lists.id as listId,
  lists.name AS listName,
  listitems.itemId,
  listitems.quantity,
  items.name,
  CAST(COALESCE(SUM(transactions.quantity), 0) AS INT) AS currentStock,
  items.warningLevel
-- 第一步：先取符合条件的清单基础数据
FROM lists
LEFT JOIN listItems ON lists.id = listitems.listId
LEFT JOIN items ON items.id = listitems.itemId
-- 第二步：和所有符合条件的地点做笛卡尔积，生成全量组合
CROSS JOIN (
  -- 如果有独立的locations表，直接查locations表更合理
  SELECT DISTINCT locationId 
  FROM transactions 
  WHERE organisationId = 1
) AS locations
-- 第三步：左连交易表，所有交易相关的过滤都写在ON里
LEFT JOIN transactions 
  ON transactions.itemId = listitems.itemId
  AND transactions.locationId = locations.locationId
  AND transactions.organisationId = 1
-- 这里只保留清单相关的过滤条件
WHERE lists.organisationId = 1 AND lists.deleted = 0
GROUP BY locations.locationId, lists.id, listitems.itemId, items.id, items.name, items.warningLevel, lists.name, listitems.quantity;

关键修改说明 #

• 新增的CROSS JOIN子句会为每个符合条件的清单商品，匹配所有存在的地点，确保你需要的所有组合都存在
• 原WHERE中的transactions.organisationId = 1移动到了LEFT JOIN的ON子句中，不会再过滤无交易记录的行
• COALESCE(SUM(transactions.quantity), 0)会把无交易的库存默认返回为0，满足你的需求
• GROUP BY子句调整为用维度表的字段分组，避免左连字段为NULL导致分组错误

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