SQL处理颜色对去重：验证自连接+窗口函数方案是否正确

问题背景 #

现有SQL colors 表结构及数据如下：

CREATE TABLE colors (
    color1 VARCHAR(50),
    color2 VARCHAR(50),
    year INT,
    var1 INT,
    var2 INT
);

INSERT INTO colors (color1, color2, year, var1, var2) VALUES
('red', 'blue', 2010, 1, 2),
('blue', 'red', 2010, 0, 2),
('green', NULL, 2010, 3, 1),
('yellow', NULL, 2010, 2, 1),
('purple', 'black', 2010, 1, 1),
('red', NULL, 2011, 5, 5),
('green', 'blue', 2011, 3, 3),
('blue', 'green', 2011, 2, 3),
('white', 'orange', 2011, 2, 3);

表数据预览：

需要完成的任务：

• 创建4个新列：color1_var1、color1_var2、color2_var1、color2_var2
• 若同年存在双向颜色对（如2010年的red-blue和blue-red），用对应信息填充新列
• 若同年无对应颜色对（如2010年的green-null或2011年的white-orange），则color2_var1和color2_var2留空为NULL
• 最终保留每年每个颜色组合的唯一行

你的方案问题点 #

你的整体思路方向正确，但存在两处明显错误：

1. 自连接条件错误：加入的(a.color2 IS NULL AND b.color2 IS NULL AND a.color1 != b.color1)条件，导致2010年的green和yellow这类单颜色行互相连接，错误地将对方的var值填充到color2_var1和color2_var2，违反了任务中"无对应颜色对时留空NULL"的要求。
2. 去重逻辑冗余：WHERE rn = 1 OR color2 IS NULL属于多余判断，单颜色行的窗口函数rn本身就是1，无需额外加OR条件。

修正后的最终方案 #

修正后的SQL代码 #

WITH color_pairs AS (
    SELECT 
        a.color1 AS color1,
        a.color2 AS color2,
        a.year AS year,
        a.var1 AS color1_var1,
        a.var2 AS color1_var2,
        b.var1 AS color2_var1,
        b.var2 AS color2_var2
    FROM 
        colors a
    LEFT JOIN 
        colors b 
    ON 
        a.year = b.year AND 
        a.color1 = b.color2 AND a.color2 = b.color1
), 
ranked_colors AS (
    SELECT 
        *,
        ROW_NUMBER() OVER (
            PARTITION BY 
                -- 按颜色对的无序组合分区，确保双向对被分到同一组
                LEAST(COALESCE(color1, ''), COALESCE(color2, '')),
                GREATEST(COALESCE(color1, ''), COALESCE(color2, '')),
                year 
            ORDER BY year
        ) AS rn
    FROM 
        color_pairs
)
SELECT 
    color1, color2, year, color1_var1, color1_var2, color2_var1, color2_var2
FROM 
    ranked_colors
WHERE 
    rn = 1;

符合要求的结果 #

这个结果完全满足所有任务要求：双向颜色对仅保留一行且正确填充对应值，无对应对的行目标列留空为NULL，每年每个颜色组合唯一。

内容的提问来源于stack exchange，提问作者Uk rain troll