基于part表process值关联多process表查询数据的问题求助

问题背景 #

现有数据库表结构如下：

part表 #

id_part process 
1         1
2         2
3         3

各process对应业务表 #

process1表

id_prs1 name     qty  id_part
1       blank     2     1
2       piercing  5     2
3       bending   6     3

process2表

id_prs2 name   qty  id_part
1       stamp  2       2
2       pierc  4       3

process3表

id_prs2 name   qty  id_part
1       cut     2       3

需求规则：

• 当part表的process值为2时，展示process1和process2的数据
• 当part表的process值为3时，展示process1、process2和process3的数据

原SQL语句仅在process=3时正常返回结果，process=2时无数据返回：

SELECT part.process, process1.name, process1.qty,process2.name, process2.qty, process3.name, process3.qty 
FROM part 
inner JOIN process1 on process1.id_part = part.id_part 
inner JOIN process2 on process2.id_part = part.id_part 
inner JOIN process3 on process3.id_part = part.id_part 
where part.id_part = '$part'

问题原因 #

INNER JOIN要求所有关联表必须存在匹配记录才会返回结果。当part的process=2时，对应的id_part（比如id_part=2）在process3表中没有对应记录，INNER JOIN process3会直接过滤掉这条数据，最终导致无结果返回。

解决方案 #

方案一：LEFT JOIN 加条件过滤 #

将INNER JOIN替换为LEFT JOIN，同时通过WHERE子句根据process值控制是否需要process3的匹配数据：

SELECT 
    part.process, 
    process1.name AS prs1_name, process1.qty AS prs1_qty,
    process2.name AS prs2_name, process2.qty AS prs2_qty,
    process3.name AS prs3_name, process3.qty AS prs3_qty
FROM part
LEFT JOIN process1 ON process1.id_part = part.id_part
LEFT JOIN process2 ON process2.id_part = part.id_part
LEFT JOIN process3 ON process3.id_part = part.id_part
WHERE 
    part.id_part = '$part'
    AND (
        part.process = 2 OR 
        (part.process = 3 AND process3.id_part IS NOT NULL)
    )

• process=2时，即使process3无匹配记录，依然会返回process1和process2的数据
• process=3时，确保process3有匹配记录才返回（若允许process3无数据时也展示，可去掉AND process3.id_part IS NOT NULL）

方案二：应用层动态拼接SQL #

在业务代码中先查询part的process值，再根据值动态生成SQL：

• 当process=2时，使用不含process3关联的SQL：

SELECT part.process, process1.name, process1.qty, process2.name, process2.qty
FROM part
INNER JOIN process1 ON process1.id_part = part.id_part
INNER JOIN process2 ON process2.id_part = part.id_part
WHERE part.id_part = '$part'

• 当process=3时，使用包含三个表关联的原SQL：

SELECT part.process, process1.name, process1.qty, process2.name, process2.qty, process3.name, process3.qty
FROM part
INNER JOIN process1 ON process1.id_part = part.id_part
INNER JOIN process2 ON process2.id_part = part.id_part
INNER JOIN process3 ON process3.id_part = part.id_part
WHERE part.id_part = '$part'

这种方式能避免LEFT JOIN带来的NULL值处理问题，逻辑更清晰。

方案三：UNION ALL 合并行记录 #

如果希望每个流程的数据单独一行展示（而非同一行拼接），可以用UNION ALL：

SELECT 'process1' AS process_type, name, qty
FROM process1
WHERE id_part = '$part'
AND EXISTS (SELECT 1 FROM part WHERE id_part = '$part' AND process >= 1)
UNION ALL
SELECT 'process2' AS process_type, name, qty
FROM process2
WHERE id_part = '$part'
AND EXISTS (SELECT 1 FROM part WHERE id_part = '$part' AND process >= 2)
UNION ALL
SELECT 'process3' AS process_type, name, qty
FROM process3
WHERE id_part = '$part'
AND EXISTS (SELECT 1 FROM part WHERE id_part = '$part' AND process >= 3)

返回结果每行对应一个流程的记录，更适合表单逐行展示的场景。

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