基于职位与角色规则标记国家的SQL查询优化问题

表结构 #

需求规则 #

生成condition_color列，规则如下：

• 若同一Role下的某国家，既存在designation为Director的记录，又存在其他designation的记录，则该国家的所有行标记为Red-[country name]
• 其他情况直接显示国家名称

原查询问题 #

原查询仅对designation为Director的行进行标记，无法覆盖同一Role下该国家的其他行，原因是CASE语句的条件仅基于当前行的designation判断，没有利用整个Role+countries分组的全局状态。

优化方案 #

方案1：使用窗口函数计算分组状态 #

SELECT
    role,
    designation,
    countries,
    CASE
        WHEN has_director = 1 AND has_other_designation = 1 
        THEN CONCAT('Red-', countries)
        ELSE countries
    END AS condition_color
FROM (
    SELECT
        *,
        -- 标记当前Role+countries分组是否存在Director
        MAX(CASE WHEN designation = 'Director' THEN 1 ELSE 0 END) OVER (PARTITION BY role, countries) AS has_director,
        -- 标记当前Role+countries分组是否存在非Director的职位
        MAX(CASE WHEN designation <> 'Director' THEN 1 ELSE 0 END) OVER (PARTITION BY role, countries) AS has_other_designation
    FROM test_data
) AS subquery;

方案2：先筛选符合条件的分组再关联 #

WITH eligible_groups AS (
    -- 筛选出同时存在Director和其他职位的Role+countries组合
    SELECT role, countries
    FROM test_data
    GROUP BY role, countries
    HAVING COUNT(CASE WHEN designation = 'Director' THEN 1 END) > 0
       AND COUNT(CASE WHEN designation <> 'Director' THEN 1 END) > 0
)
SELECT
    td.role,
    td.designation,
    td.countries,
    -- 关联上符合条件的分组则标记Red前缀
    CASE WHEN eg.role IS NOT NULL THEN CONCAT('Red-', td.countries) ELSE td.countries END AS condition_color
FROM test_data td
LEFT JOIN eligible_groups eg 
    ON td.role = eg.role AND td.countries = eg.countries;

方案说明 #

• 方案1通过窗口函数在每个Role+countries分组内计算全局状态，无需额外关联，逻辑更直观，适配大部分SQL数据库。
• 方案2先通过分组筛选出目标组合，再与原表关联，大表场景下性能更优，因为筛选后的分组数据量更小。

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