SQL查询异常：无法返回状态为2的在途预留钢卷预期结果

原SQL存在4个核心错误，直接导致在途预留钢卷无法查询、结果不符合预期：

• 错误1：WHERE条件逻辑完全错误。where b.status = 2 and c.status = 2要求钢卷同时在库存表、在途表存在且状态都为2，但实际业务中一个钢卷只会属于库存或在途其中一种状态，不存在同时匹配两张表的记录，直接导致结果为空。
• 错误2：表别名冲突、字典关联缺失。先后给coils表、material表定义了相同的别名b，会触发数据库别名冲突报错；且完全没有关联color颜色字典表，无法取到颜色名称，材质表的关联条件也写反了。
• 错误3：字段引用错误。SELECT子句中写的b.name无对应字段，coils表本身不存在name字段，只有字典表存了名称信息。
• 错误4：连接语义被破坏。对LEFT JOIN关联的coils、coils_in_transport表在WHERE层做状态过滤，会把LEFT JOIN强制转换为INNER JOIN，直接过滤掉仅匹配单表的记录，这也是在途钢卷查不出来的核心原因之一。

因为钢卷分库存、在途两个存储表，先把两张表的同结构数据合并成统一的钢卷视图，再和预留表关联、关联字典表取名称，写法最清晰也不会漏数据：

SELECT 
  a.QUANTITY,
  a.START,
  a.END,
  m.NAME AS material_name,
  c.NAME AS color_name
FROM reserved_coils a
LEFT JOIN (
  SELECT SERIAL, COLOR, MATERIAL, STATUS FROM coils
  UNION ALL
  SELECT SERIAL, COLOR, MATERIAL, STATUS FROM coils_in_transport
) t ON a.NUMBER = t.SERIAL AND t.STATUS = 2
LEFT JOIN material m ON t.MATERIAL = m.ID
LEFT JOIN color c ON t.COLOR = c.ID
WHERE t.SERIAL IS NOT NULL;

• 用UNION ALL把库存、在途两张钢卷表合并成统一的临时表t，提前在关联条件里过滤状态为2的钢卷，避免WHERE条件把LEFT JOIN变成内连接。
• 分别关联材质字典material、颜色字典color，取对应名称，别名做区分避免字段冲突。
• 最后WHERE条件判断t.SERIAL非空，是为了排除既不在库存也不在在途的无效预留钢卷记录，如果需要保留这类无效记录直接删掉该WHERE条件即可。

注：提供的测试数据中编号为A23GG6的在途钢卷状态为3，不符合状态=2的查询要求，所以最终查询结果只会返回3条记录：12345、12347、ff2S147对应的预留信息。

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