基于blocksequences等三张关联表构建目标数据表的SQL需求

根据你给出的三张表结构和需求，我整理了两种实用的SQL查询方案，专门解决S054、S036这类part在不同序列中对应不同位置的匹配问题，帮你构建准确的目标数据表：

方案一：直接关联+条件筛选转宽表 #

这个方案通过一次性关联三张表，筛选出当前序列中每个part对应的正确位置，再将结果转换为和blocksequences结构一致的宽表：

SELECT 
    bs.blocksequenceid,
    MAX(CASE WHEN pp.position = 'P1' THEN pp.partid END) AS P1,
    MAX(CASE WHEN pp.position = 'P2' THEN pp.partid END) AS P2,
    MAX(CASE WHEN pp.position = 'P3' THEN pp.partid END) AS P3,
    MAX(CASE WHEN pp.position = 'P4' THEN pp.partid END) AS P4
FROM 
    blocksequences bs
JOIN 
    blocksequenceparts bsp ON bs.blocksequenceid = bsp.blocksequenceid
JOIN 
    partpositions pp ON bsp.partid = pp.partid
-- 核心条件：确保当前part的位置和序列表中记录的位置完全匹配
WHERE 
    (pp.position = 'P1' AND pp.partid = bs.P1)
    OR (pp.position = 'P2' AND pp.partid = bs.P2)
    OR (pp.position = 'P3' AND pp.partid = bs.P3)
    OR (pp.position = 'P4' AND pp.partid = bs.P4)
GROUP BY 
    bs.blocksequenceid;

方案二：用CTE拆分逻辑（更易读） #

如果觉得上面的语句逻辑太紧凑，可以先用CTE提取出每个序列中part和位置的正确匹配关系，再转换为宽表，逻辑更清晰：

WITH sequence_part_matches AS (
    SELECT 
        bsp.blocksequenceid,
        bsp.partid,
        pp.position
    FROM 
        blocksequenceparts bsp
    JOIN 
        partpositions pp ON bsp.partid = pp.partid
    JOIN 
        blocksequences bs ON bsp.blocksequenceid = bs.blocksequenceid
    WHERE 
        (pp.position = 'P1' AND pp.partid = bs.P1)
        OR (pp.position = 'P2' AND pp.partid = bs.P2)
        OR (pp.position = 'P3' AND pp.partid = bs.P3)
        OR (pp.position = 'P4' AND pp.partid = bs.P4)
)
SELECT 
    blocksequenceid,
    MAX(CASE WHEN position = 'P1' THEN partid END) AS P1,
    MAX(CASE WHEN position = 'P2' THEN partid END) AS P2,
    MAX(CASE WHEN position = 'P3' THEN partid END) AS P3,
    MAX(CASE WHEN position = 'P4' THEN partid END) AS P4
FROM 
    sequence_part_matches
GROUP BY 
    blocksequenceid;

额外需求：长表格式 #

如果你需要的是每行对应一个位置的长表（而不是宽表），直接用下面的查询即可：

SELECT 
    bsp.blocksequenceid,
    bsp.partid,
    pp.position
FROM 
    blocksequenceparts bsp
JOIN 
    partpositions pp ON bsp.partid = pp.partid
JOIN 
    blocksequences bs ON bsp.blocksequenceid = bs.blocksequenceid
WHERE 
    (pp.position = 'P1' AND pp.partid = bs.P1)
    OR (pp.position = 'P2' AND pp.partid = bs.P2)
    OR (pp.position = 'P3' AND pp.partid = bs.P3)
    OR (pp.position = 'P4' AND pp.partid = bs.P4);

结果验证 #

用你给出的示例数据测试，宽表查询会返回和blocksequences一致的结果，长表查询则会输出每个part在对应序列中的准确位置，完美解决同一part对应多位置的歧义问题。

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