如何为数据集分配递增行号：连续匹配字段时重复行号

需要为包含唯一ID但其他字段可能重复的大数据集分配行号，要求：

• 行号按ID顺序递增
• 当连续行的isLength和isThickness字段与上一行完全匹配时，重复该行号

尝试过Rank()、Dense_Rank()结合分区与Order By，但未达到预期效果，寻求可行方案。

示例数据代码 #

DECLARE @tempData Table (ID INT Identity(1,1), isLength INT, isThickness nvarchar(6))

INSERT INTO @tempData(isLength, isThickness)
Values(126,'1.375')
    ,(122,'1.375')
    ,(120,'1.375')
    ,(120,'1.375')
    ,(120,'3')
    ,(194,'2')
    ,(242,'2')
    ,(242,'2')
    ,(122,'1.375')
    ,(122,'1.375')
    ,(108,'1.75')
    ,(60,'1.75')
    ,(108,'1.75')

SELECT ID, isLength, isThickness
FROM @tempData t
Order BY ID

期望结果 #

解决方案 #

可以通过窗口函数标记分组，再累计分组数实现需求，核心逻辑是识别连续重复的行组，为同一组分配相同行号：

SELECT 
    ID,
    isLength,
    isThickness,
    SUM(GroupFlag) OVER (ORDER BY ID) AS [Desired Row Number]
FROM (
    SELECT 
        ID,
        isLength,
        isThickness,
        -- 对比当前行与上一行的字段，不同则标记为1，相同为0
        CASE 
            WHEN LAG(isLength) OVER (ORDER BY ID) = isLength 
                 AND LAG(isThickness) OVER (ORDER BY ID) = isThickness
            THEN 0
            ELSE 1
        END AS GroupFlag
    FROM @tempData
) t
ORDER BY ID

代码说明 #

• LAG()函数：按ID排序后，获取上一行的isLength和isThickness值，用于和当前行对比
• CASE语句：当当前行与上一行的两个字段完全匹配时标记为0，否则标记为1（表示新行组开始）
• SUM(GroupFlag) OVER (ORDER BY ID)：按ID顺序累计标记值，同一连续组内标记值为0，累计值保持不变，实现重复行号；新组开始时标记值为1，累计值递增

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