如何在SQL中实现对称配对输出？缺失配对默认Score为0

安全阿岳

2026-6-21

实现双向配对补全的SQL方案

可以实现，下面给出两种可行的实现方法：

方法一：补充缺失的反向配对记录

这种方法先保留原表所有记录，再找出存在正向配对但反向缺失的组合，补充Score为0的记录，最终合并结果。

WITH missing_reverse_pairs AS (
    -- 筛选出需要补充的反向配对
    SELECT 
        t1.Type2 AS Type1,
        t1.Type1 AS Type2,
        0 AS Score
    FROM your_table t1
    WHERE NOT EXISTS (
        -- 检查反向配对是否已存在
        SELECT 1 
        FROM your_table t2
        WHERE t2.Type1 = t1.Type2 AND t2.Type2 = t1.Type1
    )
)
-- 合并原表记录与补充的缺失记录
SELECT Type1, Type2, Score FROM your_table
UNION ALL
SELECT Type1, Type2, Score FROM missing_reverse_pairs
ORDER BY Type1, Type2;

方法二：生成所有必要双向配对，左连接补全Score

这种方法先提取所有出现过的Type值，生成所有存在至少一个方向配对的双向组合，再通过左连接原表获取Score，缺失项用0填充。

WITH unique_types AS (
    -- 提取所有出现过的Type值
    SELECT Type1 AS type FROM your_table
    UNION
    SELECT Type2 AS type FROM your_table
),
required_pairs AS (
    -- 生成所有需要的双向配对（仅保留原表存在至少一个方向的组合）
    SELECT 
        ut1.type AS Type1,
        ut2.type AS Type2
    FROM unique_types ut1
    CROSS JOIN unique_types ut2
    WHERE EXISTS (
        SELECT 1 
        FROM your_table t
        WHERE (t.Type1 = ut1.type AND t.Type2 = ut2.type) 
           OR (t.Type1 = ut2.type AND t.Type2 = ut1.type)
    )
)
-- 左连接原表，补全缺失的Score为0
SELECT 
    rp.Type1,
    rp.Type2,
    COALESCE(t.Score, 0) AS Score
FROM required_pairs rp
LEFT JOIN your_table t 
    ON rp.Type1 = t.Type1 AND rp.Type2 = t.Type2
ORDER BY rp.Type1, rp.Type2;