如何在SQL中按类型分组并按优先级规则返回对应优先级值

我需要对数据按type字段进行分组，同时结合优先级等级规则返回每个type对应的优先级值，优先级从高到低为high到none，具体规则如下：

• 若某type存在priority为high的记录，则该type的输出优先级为high
• 若某type无high优先级记录，但存在medium优先级记录，则输出为medium
• 若某type无high、medium优先级记录，但存在low优先级记录，则输出为low
• 若某type仅存在none优先级记录，则输出为none

示例测试数据 #

WITH classif AS
(
    select 1 as id, 'account' as type, 'high' as priority from dual union all
    select 2 as id, 'account' as type, 'none' as priority from dual union all
    select 3 as id, 'account' as type, 'medium' as priority from dual union all
    select 4 as id, 'security' as type, 'high' as priority from dual union all
    select 5 as id, 'security' as type, 'medium' as priority from dual union all
    select 6 as id, 'security' as type, 'low' as priority from dual union all
    select 7 as id, 'security' as type, 'none' as priority from dual union all
    select 8 as id, 'transform' as type, 'none' as priority from dual union all
    select 9 as id, 'transform' as type, 'none' as priority from dual union all
    select 10 as id, 'transform' as type, 'none' as priority from dual union all
    select 11 as id, 'transform' as type, 'none' as priority from dual union all
    select 12 as id, 'enrollment' as type, 'medium' as priority from dual union all
    select 13 as id, 'enrollment' as type, 'low' as priority from dual union all
    select 14 as id, 'enrollment' as type, 'low' as priority from dual union all
    select 15 as id, 'enrollment' as type, 'low' as priority from dual union all
    select 15 as id, 'process' as type, 'low' as priority from dual union all
    select 15 as id, 'process' as type, 'none' as priority from dual union all
    select 15 as id, 'process' as type, 'none' as priority from dual
)

注：原始CTE存在多处多余分号，已修改为union all保证语法正确。

期望输出 #

------------+-------------
type        |  priority
------------+-------------
account     |  high
security    |  high
transform   |  none
enrollment  |  medium
process     |  low
---------------------------

原有问题代码 #

select 
    type,
    case 
        when priority = 'high' then 'high'
        when priority = 'medium' and priority <> 'high' then 'medium'
        when priority = 'medium' and priority <> 'high' then 'medium'
        when priority = 'low' and priority <> 'high' or priority <> 'medium' then 'low'
        when priority = 'none' and priority <> 'high' or priority <> 'medium' or priority <> 'low' then 'none'
        end as priority        
from classif
group by type,
    case
    when priority = 'high' then 'high'
        when priority = 'medium' and priority <> 'high' then 'medium'
        when priority = 'medium' and priority <> 'high' then 'medium'
        when priority = 'low' and priority <> 'high' or priority <> 'medium' then 'low'
        when priority = 'none' and priority <> 'high' or priority <> 'medium' or priority <> 'low' then 'none'
        end;

错误原因分析 #

1. 你将priority转换后的值也放入了GROUP BY子句，会导致同一个type下不同优先级的记录被拆分成多个分组，最终返回多行结果。
2. 行级的CASE判断只能读取当前行的priority值，无法感知同个type下其他行的优先级情况，不符合"取同type最高优先级"的需求。
3. 原有CASE中的逻辑判断存在运算符优先级错误，OR优先级低于AND，会导致判断结果不符合预期。

正确SQL写法 #

核心思路是先给不同优先级映射权重数值（数值越高优先级越高），按type分组后取每个分组的最大权重，再将权重映射回优先级字符串即可：

SELECT 
    type,
    CASE MAX(CASE priority
                WHEN 'high' THEN 4
                WHEN 'medium' THEN 3
                WHEN 'low' THEN 2
                ELSE 1 
            END)
        WHEN 4 THEN 'high'
        WHEN 3 THEN 'medium'
        WHEN 2 THEN 'low'
        ELSE 'none'
    END AS priority
FROM classif
GROUP BY type;

执行后输出结果完全匹配预期。

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