SQL技术咨询：如何基于多列值生成判断列Column C？

问题背景 #

原始数据表：

需要生成包含Column C的目标表，规则如下：

• 若同一Column A对应的Column B唯一值仅为9999，Column C为red
• 若同一Column A对应的Column B存在多个不同值（即使包含9999），Column C为blue

之前尝试SELECT DISTINCT Column A, COUNT(DISTINCT Column B)仅能统计分组唯一值数量，无法关联回原表生成每行的标记列，以下是可行方案：

方案1：窗口函数实现 #

利用窗口函数按Column A分组计算唯一值数量，结合CASE语句生成标记：

SELECT 
    "Column A",
    "Column B",
    CASE 
        WHEN COUNT(DISTINCT "Column B") OVER (PARTITION BY "Column A") = 1 
             AND MAX("Column B") OVER (PARTITION BY "Column A") = '9999' 
        THEN 'red'
        ELSE 'blue'
    END AS "Column C"
FROM your_table;

方案2：子查询关联实现 #

先通过子查询统计每个Column A的唯一值情况，再关联原表映射标记：

WITH policy_b_stats AS (
    SELECT 
        "Column A",
        COUNT(DISTINCT "Column B") AS distinct_b_count,
        MAX("Column B") AS max_b_value
    FROM your_table
    GROUP BY "Column A"
)
SELECT 
    t."Column A",
    t."Column B",
    CASE 
        WHEN p.distinct_b_count = 1 AND p.max_b_value = '9999' 
        THEN 'red'
        ELSE 'blue'
    END AS "Column C"
FROM your_table t
JOIN policy_b_stats p ON t."Column A" = p."Column A";

方案说明 #

• 窗口函数方案无需额外子查询，直接在原表查询中完成分组统计与标记生成，语法简洁
• 子查询方案先预统计分组信息，适合复杂场景下的逻辑扩展，可读性更强
• 两个方案都通过判断分组内Column B的唯一值数量是否为1且唯一值是9999，来确定标记颜色

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