SQL按年份保留符合条件的颜色对唯一实例问题咨询

表结构与原始数据 #

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

INSERT INTO colors (color1, color2, year, var1, var2, var3, var4) VALUES
    ('red', 'blue', 2010, 1, 2, 1, 2),
    ('blue', 'red', 2010, 1, 2, 1, 2),
    ('red', 'blue', 2011, 1, 2, 5, 3),
    ('blue', 'red', 2011, 5, 3, 1, 2),
    ('orange', NULL, 2010, 5, 9, NULL, NULL),
    ('green', 'white', 2010, 5, 9, 6, 3);

原始数据：

color1 color2 year var1 var2 var3 var4
    red   blue 2010    1    2    1    2
   blue    red 2010    1    2    1    2
    red   blue 2011    1    2    5    3
   blue    red 2011    5    3    1    2
 orange   NULL 2010    5    9 NULL NULL
green    white 2010    5    9    6    3

需求说明 #

• 同一年份的镜像颜色对（如2010年的red/blue和blue/red）：若每行的var1=var3且var2=var4，仅保留其中一行；
• 同一年份的镜像颜色对：若任意一行的var1≠var3或var2≠var4，保留两行；
• 无镜像颜色对的行全部保留。

预期结果 #

color1 color2 year var1 var2 var3 var4
    red   blue 2010    1    2    1    2
    red   blue 2011    1    2    5    3
   blue    red 2011    5    3    1    2
 orange   NULL 2010    5    9 NULL NULL
green    white 2010    5    9    6    3

你的SQL代码及输出 #

WITH pairs AS (
    SELECT *,
    CASE 
        WHEN color1 < color2 THEN color1 || color2 || CAST(year AS VARCHAR(4))
        ELSE color2 || color1 || CAST(year AS VARCHAR(4))
    END AS pair_id
    FROM colors
),
ranked_pairs AS (
    SELECT *,
    ROW_NUMBER() OVER(PARTITION BY pair_id ORDER BY color1, color2) as row_num
    FROM pairs
)
SELECT color1, color2, year, var1, var2, var3, var4
FROM ranked_pairs
WHERE row_num = 1 OR var1 != var3 OR var2 != var4;

输出结果：

color1 color2 year var1 var2 var3 var4
 orange   <NA> 2010    5    9   NA   NA
   blue    red 2010    1    2    1    2
   blue    red 2011    5    3    1    2
    red   blue 2011    1    2    5    3
  green  white 2010    5    9    6    3

代码正确性验证与边缘情况分析 #

你的代码在当前测试数据下表现符合需求，以下是关键边缘场景的验证：

1. 镜像对中一行满足变量条件、另一行不满足 #

假设插入数据：

INSERT INTO colors VALUES ('red', 'blue', 2020, 1,2,1,2), ('blue', 'red',2020,1,2,3,4);

• 第一行var1=var3且var2=var4，第二行var1≠var3
• 代码会保留两行：第一行因row_num=1被保留，第二行因var1≠var3被保留，完全符合需求。

2. 含NULL的镜像颜色对 #

若存在：

INSERT INTO colors VALUES ('red', NULL,2020,1,2,1,2), (NULL,'red',2020,1,2,1,2);

• 由于color1 < color2与NULL比较结果为NULL，两行的pair_id均为NULL
• 多数数据库中PARTITION BY会将NULL值归为同一分区，因此两行会被分到同一组，row_num为1和2
• 因两行都满足var1=var3且var2=var4，代码仅保留row_num=1的行，这符合“镜像对保留一行”的需求（若你认为NULL参与的颜色对属于镜像对）。

3. 同色镜像对 #

若存在：

INSERT INTO colors VALUES ('red','red',2020,1,2,1,2), ('red','red',2020,1,2,1,2);

• 两行的pair_id均为redred2020，被分到同一分区
• 代码仅保留row_num=1的行，符合需求。

4. 镜像对两行均不满足变量条件 #

若存在：

INSERT INTO colors VALUES ('red','blue',2020,1,2,3,4), ('blue','red',2020,5,6,7,8);

• 两行均满足var1≠var3或var2≠var4，代码会保留两行，符合需求。

潜在问题 #

• 字符串拼接兼容性：||运算符在部分数据库（如MySQL）中需启用特定模式或替换为CONCAT函数，否则会报错。
• NULL颜色对的定义：若你认为含NULL的颜色对不属于镜像对，需调整pair_id的生成逻辑，例如当color2 IS NULL时，单独生成唯一ID。

结论 #

你的代码核心逻辑符合需求，能覆盖绝大多数常规场景；对于含NULL的边缘情况，若你的需求将NULL参与的颜色对视为镜像对，代码逻辑是正确的，否则需微调pair_id的生成规则。

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