如何按员工分组为超1000的Loan Number分配未占用低编号？

需求回顾 #

• 按员工（EMPLOYEE）分组处理贷款编号：
  • 原有小于1000的Loan Number保持不变
  • 大于1000的Loan Number，需替换为该员工未使用的最小可用编号，且按原大编号的从小到大顺序，依次对应最小的可用编号（不能重复使用已有的编号）

问题痛点 #

直接使用ROW_NUMBER()无法自动排除员工已占用的编号，导致分配的新编号会和原有小编号重复。

解决方案思路 #

1. 提取每个员工已有的所有小于1000的贷款编号，作为已占用集合
2. 为每个员工生成连续数字序列，过滤掉已占用的编号，得到有序的可用编号列表
3. 对每个员工的大于1000的贷款编号按从小到大排序，为其分配排名，再匹配可用编号列表中对应排名的编号
4. 合并原有小编号记录和重分配后的记录，输出最终结果

完整SQL代码 #

WITH EmployeeLoans AS (
    SELECT 
        EMPLOYEE,
        LOANNUMBER,
        -- 为需要重分配的大编号按原顺序生成排名
        CASE WHEN LOANNUMBER > 1000 THEN ROW_NUMBER() OVER (PARTITION BY EMPLOYEE ORDER BY LOANNUMBER) END AS ReassignRank
    FROM #EmployeeLoanNumbers
),
AvailableNumbers AS (
    SELECT 
        el.EMPLOYEE,
        num.Number AS AvailableNumber,
        -- 为可用编号生成排名，用于匹配大编号的重分配顺序
        ROW_NUMBER() OVER (PARTITION BY el.EMPLOYEE ORDER BY num.Number) AS Rank
    FROM (
        -- 生成足够多的连续数字（此处取1-1000，可根据实际需求调整范围）
        SELECT TOP 1000 ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS Number
        FROM sys.all_columns
    ) num
    CROSS JOIN (SELECT DISTINCT EMPLOYEE FROM #EmployeeLoanNumbers) el
    -- 左连接过滤已占用的小编号
    LEFT JOIN (
        SELECT EMPLOYEE, LOANNUMBER FROM #EmployeeLoanNumbers WHERE LOANNUMBER <= 1000
    ) used 
        ON el.EMPLOYEE = used.EMPLOYEE AND num.Number = used.LOANNUMBER
    WHERE used.LOANNUMBER IS NULL
)
-- 合并输出结果
SELECT 
    el.EMPLOYEE,
    el.LOANNUMBER,
    CASE 
        WHEN el.LOANNUMBER <= 1000 THEN NULL -- 原有小编号保持空值，匹配示例格式
        ELSE an.AvailableNumber 
    END AS DESIRED_NEW_LOAN_NUMBER
FROM EmployeeLoans el
LEFT JOIN AvailableNumbers an 
    ON el.EMPLOYEE = an.EMPLOYEE AND el.ReassignRank = an.Rank
ORDER BY el.EMPLOYEE, el.LOANNUMBER;

代码说明 #

• EmployeeLoans CTE：标记需要重分配的记录，同时为每个员工的大编号按原始顺序生成排名，确保后续分配顺序正确
• AvailableNumbers CTE：通过生成连续数字序列，结合左连接排除已占用编号，为每个员工生成有序的可用编号列表
• 最终查询：将原有记录与可用编号列表按员工和排名关联，完成重分配逻辑，原有小编号输出NULL以匹配示例格式

测试验证 #

执行上述代码后，输出结果将完全匹配示例中的DESIRED NEW LOAN NUMBER列内容。

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