如何编写TSQL查询为分组重复用户生成前后行ID用于合并？

你可以通过SQL Server的LEAD窗口函数快速实现需求，完整查询如下：

WITH UserMergeGroup AS (
    SELECT
        FullName AS 姓名,
        Birthdate AS 出生日期,
        UserID AS [合并源ID（Merge From）],
        LEAD(UserID) OVER (
            PARTITION BY FullName, Birthdate 
            ORDER BY UserID ASC
        ) AS [合并目标ID（Merge To）]
    FROM #tmpUsers
)
SELECT 姓名, 出生日期, [合并源ID（Merge From）], [合并目标ID（Merge To）]
FROM UserMergeGroup
WHERE [合并目标ID（Merge To）] IS NOT NULL
ORDER BY 姓名, 出生日期, [合并源ID（Merge From）];

实现逻辑说明 #

• 用PARTITION BY FullName, Birthdate将姓名、出生日期完全相同的用户划分为同一分组
• 每个分组内按UserID升序排序，符合你将小号合并到大号、最终保留最大ID的需求
• LEAD窗口函数直接提取分组内当前行的下一个UserID作为合并目标，每个分组最大的ID没有下一行值，会返回NULL，过滤掉这部分行即可得到全部需要执行的合并映射关系

如果需要把结果存入临时表供后续流程使用，只需要在SELECT后增加INTO #tmpMergeMapping即可，示例：

WITH UserMergeGroup AS (
    SELECT
        FullName AS 姓名,
        Birthdate AS 出生日期,
        UserID AS [合并源ID（Merge From）],
        LEAD(UserID) OVER (
            PARTITION BY FullName, Birthdate 
            ORDER BY UserID ASC
        ) AS [合并目标ID（Merge To）]
    FROM #tmpUsers
)
SELECT 姓名, 出生日期, [合并源ID（Merge From）], [合并目标ID（Merge To）]
INTO #tmpMergeMapping
FROM UserMergeGroup
WHERE [合并目标ID（Merge To）] IS NOT NULL
ORDER BY 姓名, 出生日期, [合并源ID（Merge From）];

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