SQL实现同ID下按Group优先级统一填充Result值的需求问询

问题背景 #

提问者目前使用CASE语句根据条目所属的Group字段返回对应的Result值，现有代码如下：

Case
when [Group] = 'b' or [Group] = 'e'
then "Blue"
when [Group] = 'c'
then "red"
when [ Group] = 'd'
then "green"

执行后得到的结果表如下：

但提问者想要实现的是：同一ID下的所有条目，统一使用该ID内优先级最高的Group对应的Result值。优先级规则为：d（green）> c（red）> b/e（Blue）> a（即当ID包含d时，所有行都显示green；包含c但无d时，所有行显示red；包含b/e但无c、d时，所有行显示Blue；仅包含a时，才显示a对应的结果）。期望的最终结果表如下：

提问者未在CASE中处理Group='a'的情况，因为仅希望当同ID无其他高优先级Group时，a才返回自身对应的结果，现在需要修改代码实现该需求。

解决方案 #

要实现这个逻辑，核心是先为每个ID确定其对应的最高优先级Result，再将该值应用到该ID的所有行上。这里推荐使用窗口函数来实现，以下是具体的实现方案：

方案一：使用CTE+窗口函数（可读性高）

先通过CTE为每个Group生成对应的Result和优先级数值，再按ID分组取最高优先级对应的Result：

WITH group_priorities AS (
    SELECT 
        ID,
        [Group],
        -- 生成每个Group对应的Result
        CASE 
            WHEN [Group] IN ('b', 'e') THEN 'Blue'
            WHEN [Group] = 'c' THEN 'red'
            WHEN [Group] = 'd' THEN 'green'
            WHEN [Group] = 'a' THEN 'a' -- 可根据需求修改a对应的Result
        END AS individual_result,
        -- 给Group分配优先级，数值越大优先级越高
        CASE 
            WHEN [Group] = 'd' THEN 4
            WHEN [Group] = 'c' THEN 3
            WHEN [Group] IN ('b', 'e') THEN 2
            WHEN [Group] = 'a' THEN 1
        END AS priority_rank
    FROM your_table_name -- 替换为你的表名
)
SELECT 
    ID,
    [Group],
    -- 取当前ID下优先级最高的Result，应用到所有行
    MAX(individual_result) OVER (
        PARTITION BY ID 
        ORDER BY priority_rank DESC 
        ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
    ) AS Result
FROM group_priorities;

方案二：直接嵌套窗口函数（更简洁）

如果不想用CTE，可以直接在SELECT中嵌套窗口函数，逻辑和方案一一致：

SELECT 
    ID,
    [Group],
    FIRST_VALUE(
        CASE 
            WHEN [Group] = 'd' THEN 'green'
            WHEN [Group] = 'c' THEN 'red'
            WHEN [Group] IN ('b', 'e') THEN 'Blue'
            WHEN [Group] = 'a' THEN 'a'
        END
    ) OVER (
        PARTITION BY ID 
        ORDER BY 
            CASE 
                WHEN [Group] = 'd' THEN 4
                WHEN [Group] = 'c' THEN 3
                WHEN [Group] IN ('b', 'e') THEN 2
                WHEN [Group] = 'a' THEN 1
            END DESC
        ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
    ) AS Result
FROM your_table_name;

逻辑说明 #

1. 优先级赋值：我们给每个Group分配了优先级数值，确保d的优先级最高，a最低，这样排序时能准确找到每个ID下的最高优先级Group。
2. 窗口函数作用：PARTITION BY ID确保我们只在同一个ID的范围内计算，ORDER BY priority_rank DESC让优先级最高的Group排在前面，MAX()或FIRST_VALUE()则会把这个最高优先级对应的Result提取出来，应用到该ID的所有行上。
3. 特殊情况处理：如果你的需求中，当ID只有a时需要返回NULL或者特定值，只需修改CASE语句中WHEN [Group] = 'a'的分支即可。

备注：内容来源于stack exchange，提问作者rvandy