在SQL Server中查询重复Alias并生成对应Value列的解决方案

需求明确 #

现有表结构（示例）：

期望输出：

SQL Server解决方案 #

使用窗口函数COUNT()和ROW_NUMBER()实现规则，适合百万级数据量，比Excel公式高效得多：

SELECT 
    ID,
    Alias,
    Value AS Value1,
    -- 非重复Alias：Value2等于Value1；重复Alias仅首行显示Value1，其余为NULL
    CASE 
        WHEN Alias_Count = 1 THEN Value
        WHEN Row_Num = 1 THEN Value
        ELSE NULL
    END AS Value2
FROM (
    SELECT 
        ID,
        Alias,
        Value,
        -- 统计每个Alias的总记录数
        COUNT(*) OVER (PARTITION BY Alias) AS Alias_Count,
        -- 按ID排序，标记每个Alias组内的行号
        ROW_NUMBER() OVER (PARTITION BY Alias ORDER BY ID) AS Row_Num
    FROM YourTableName  -- 替换为你的实际表名
) AS SubQuery
ORDER BY ID;

代码解释 #

1. 子查询中：
   • COUNT(*) OVER (PARTITION BY Alias)：计算每个Alias对应的记录总数，用于判断是否为重复Alias
   • ROW_NUMBER() OVER (PARTITION BY Alias ORDER BY ID)：给每个Alias组内的记录按ID升序编号，首行编号为1
2. 外层查询的CASE逻辑：
   • 如果Alias是唯一的（总数=1），Value2直接取当前行的Value
   • 如果是重复Alias的首行（行号=1），Value2取当前行的Value
   • 其余重复Alias的行，Value2设为NULL

性能优化建议 #

如果表中数据量较大（如20万行），建议给Alias字段创建非聚集索引，提升窗口函数的计算效率：

CREATE NONCLUSTERED INDEX IX_YourTableName_Alias ON YourTableName (Alias) INCLUDE (ID, Value);

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