如何通过SQL生成同ID分区内POC与SID的全组合关联结果？

要实现每个ID分区内(POC, SID)的全组合并关联对应VALUE，核心思路是先获取每个ID对应的所有唯一SID，再与同ID下的POC做笛卡尔积，最后左关联原表补全VALUE：

完整SQL语句 #

WITH id_unique_sids AS (
    -- 提取每个ID下的所有唯一SID
    SELECT DISTINCT ID, SID
    FROM t1
)
SELECT 
    t2.ID,
    t2.POC,
    s.SID,
    t1.VALUE
FROM t2
-- 同ID下，POC与SID做全组合
CROSS JOIN id_unique_sids s ON t2.ID = s.ID
-- 左关联原表匹配对应VALUE，无匹配则返回NULL
LEFT JOIN t1 
    ON t2.ID = t1.ID 
    AND t2.POC = t1.POC 
    AND s.SID = t1.SID
ORDER BY t2.ID, s.SID, t2.POC;

逻辑说明 #

1. CTE id_unique_sids：从t1中提取每个ID对应的所有唯一SID，确保每个ID下的SID不重复。
2. 交叉连接（CROSS JOIN）：将t2中每个ID的POC列表，与同ID的SID列表做笛卡尔积，生成该ID下(POC, SID)的所有可能组合。
3. 左关联（LEFT JOIN）：基于ID、POC、SID三个字段关联t1，把存在的VALUE值补全，不存在的组合则返回NULL。
4. 排序：按ID、SID、POC排序，让结果和需求示例的顺序一致。

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