基于多行数据按Color优先级为唯一ID设置默认值

嘿，这个需求我之前也碰到过，用窗口函数就能完美解决！核心思路是先给每种颜色按你指定的优先级（BROWN>BLACK>BLUE）分配权重，再给每个ID下的颜色按权重排序，最后取权重最高的那个颜色即可。

方法一：使用ROW_NUMBER()窗口函数（推荐） #

这种方法最灵活，哪怕有其他颜色混入也能轻松处理，而且逻辑清晰：

-- 先给每个颜色按优先级打分数，再分组排序取第一
WITH ranked_colors AS (
    SELECT 
        ID,
        Color,
        -- 按优先级分配分数：BROWN最高（3），BLACK次之（2），BLUE最低（1）
        ROW_NUMBER() OVER (
            PARTITION BY ID 
            ORDER BY 
                CASE Color 
                    WHEN 'BROWN' THEN 3 
                    WHEN 'BLACK' THEN 2 
                    WHEN 'BLUE' THEN 1 
                    ELSE 0 -- 其他颜色优先级最低，会被排到最后
                END DESC
        ) AS rank_num
    FROM your_joined_table -- 替换成你的实际表名
)
SELECT ID, Color AS Preferred_Color
FROM ranked_colors
WHERE rank_num = 1; -- 取每个ID下优先级最高的颜色

逻辑解释： #

1. 用CTE（公共表表达式）ranked_colors给每条数据标记优先级排名：
   • PARTITION BY ID：按ID分组，确保只在同一个ID内排序
   • ORDER BY CASE...DESC：把颜色转成数字权重，降序排列意味着优先级高的颜色排前面
   • ROW_NUMBER()：给每个ID下的颜色按优先级分配序号，最高优先级的序号是1
2. 最后筛选序号为1的记录，就是每个ID对应的目标颜色

方法二：聚合函数结合CASE（适合无其他颜色的场景） #

如果你的表只有这三种颜色，也可以用聚合函数简化写法，但要注意字符串排序的特性（不如窗口函数直观）：

SELECT 
    ID,
    -- 利用MAX函数取优先级最高的颜色，因为字符串排序中BROWN > BLACK > BLUE
    MAX(CASE 
        WHEN Color = 'BROWN' THEN 'BROWN'
        WHEN Color = 'BLACK' THEN 'BLACK'
        WHEN Color = 'BLUE' THEN 'BLUE'
    END) AS Preferred_Color
FROM your_joined_table
GROUP BY ID;

注意事项： #

• 记得把your_joined_table替换成你实际的JOIN后表名
• 如果需要忽略其他颜色，可以在CTE的SELECT语句里加WHERE Color IN ('BROWN','BLACK','BLUE')
• 要是优先级规则有变动，只需要调整CASE里的数字或顺序就行

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