MySQL中ORDER BY FIELD排序时显示空part_id对应行的实现问询

你的问题核心在于内连接（INNER JOIN）只会返回两张表中完全匹配的行，所以当part_id为8、6没有对应数据时，这些行就被过滤掉了。要实现「即使无数据也要显示、默认stock_deduct为1」的需求，我们需要先构造一个包含所有目标part_id的数据集，再通过**左连接（LEFT JOIN）**关联原表，最后处理默认值。

方法一：兼容所有MySQL版本的写法 #

通过UNION ALL手动生成需要的part_id列表，再左连接原表：

SELECT 
    IFNULL(bom_part.stock_deduct, 1) AS stock_deduct,
    bom_part.GRN_id,
    target_parts.part_id
FROM (
    -- 构造包含所有目标part_id的临时数据集
    SELECT 34 AS part_id UNION ALL
    SELECT 8 AS part_id UNION ALL
    SELECT 36 AS part_id UNION ALL
    SELECT 6 AS part_id
) AS target_parts
LEFT JOIN bom ON bom.part_id = target_parts.part_id
LEFT JOIN bom_part ON bom_part.BOM_id = bom.id 
    AND bom_part.batch_id = '0' 
    AND bom.sr_no = 23  -- 将筛选条件移到JOIN中，避免过滤掉无匹配的行
ORDER BY FIELD(target_parts.part_id, 34, 8, 36, 6);

方法二：MySQL 8.0+ 简洁写法 #

利用VALUES子句直接生成目标part_id列表：

SELECT 
    COALESCE(bom_part.stock_deduct, 1) AS stock_deduct,
    bom_part.GRN_id,
    target_parts.part_id
FROM (
    VALUES ROW(34), ROW(8), ROW(36), ROW(6)
) AS target_parts(part_id)
LEFT JOIN bom ON bom.part_id = target_parts.part_id
LEFT JOIN bom_part ON bom_part.BOM_id = bom.id 
    AND bom_part.batch_id = '0' 
    AND bom.sr_no = 23
ORDER BY FIELD(target_parts.part_id, 34, 8, 36, 6);

关键细节说明 #

1. 临时数据集：先定义所有需要显示的part_id，确保即使没有匹配数据，这些行也会被保留。
2. 左连接替代内连接：左连接会保留左表（临时数据集）的所有行，右表无匹配时返回NULL。
3. 默认值处理：用IFNULL或COALESCE把stock_deduct的NULL值替换为1。
4. 筛选条件位置：将batch_id='0'和sr_no=23移到LEFT JOIN的ON子句中，而不是WHERE子句，否则会把无匹配的行再次过滤掉。

这样执行后，就能得到你预期的结果：part_id为8、6的行会被显示，stock_deduct设为1，且排序完全符合你指定的顺序。

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