如何在SQL Server中创建标识列判断分组内列值是否存在差异

需求 #

按ID分组，新增Flag列：

• 若同一ID分组内的Code值完全一致，Flag设为0
• 若同一ID分组内的Code存在不同值，Flag设为1

示例原始数据 #

ID         City       Code
AB123      London     CA1
AB123      New York   CA1
CD321      Paris      CA1
CD321      Tokyo      DW2

期望结果 #

ID         City       Code  Flag
AB123      London     CA1    0
AB123      New York   CA1    0
CD321      Paris      CA1    1
CD321      Tokyo      DW2    1

用户尝试的无效语句 #

仅实现了按CODE+ID分组生成行号，未达成需求：

SELECT ID, City, Code, 
       ROW_NUMBER() OVER (PARTITION BY CODE, ID ORDER BY ID) as Flag
FROM table
ORDER BY ID

解决方案 #

核心思路 #

通过统计每个ID分组内Code的不同值数量来判断：

• 不同值数量=1 → 组内无差异，Flag=0
• 不同值数量>1 → 组内有差异，Flag=1

方案1：支持窗口函数DISTINCT的数据库（PostgreSQL、SQL Server、MySQL 8.0+等） #

直接用窗口函数统计分组内的唯一值数量，再通过CASE赋值：

SELECT 
    ID, 
    City, 
    Code,
    CASE 
        WHEN COUNT(DISTINCT Code) OVER (PARTITION BY ID) = 1 THEN 0
        ELSE 1
    END AS Flag
FROM your_table
ORDER BY ID;

方案2：不支持窗口函数DISTINCT的数据库（如MySQL 5.x） #

先通过子查询预统计每个ID的唯一Code数量，再关联原表生成Flag：

SELECT 
    t.ID, 
    t.City, 
    t.Code,
    CASE 
        WHEN cnt = 1 THEN 0
        ELSE 1
    END AS Flag
FROM your_table t
JOIN (
    SELECT ID, COUNT(DISTINCT Code) AS cnt
    FROM your_table
    GROUP BY ID
) g ON t.ID = g.ID
ORDER BY t.ID;

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