SQL Server：为现有Customer表批量分配同组LicenceId的一次性更新

嘿，这个场景我熟，不用游标也能轻松搞定，核心就是利用窗口函数做同组内的一一配对，完全避免多匹配的问题。下面是具体步骤：

1. 先给Customer表新增LicenceId外键列 #

首先得把列加上，再建外键约束，因为初始值是NULL，所以不会有冲突：

-- 新增LicenceId列，允许空值，初始默认NULL
ALTER TABLE Customer
ADD LicenceId INT NULL;

-- 添加外键约束，关联Licence表的主键
ALTER TABLE Customer
ADD CONSTRAINT FK_Customer_Licence 
FOREIGN KEY (LicenceId) REFERENCES Licence(LicenceId);

2. 一次性批量分配LicenceId（核心操作） #

这里的关键是用ROW_NUMBER()窗口函数，给每个Group下的Customer和Licence分别生成组内唯一序号，然后按组和序号一一匹配。因为题目里说每个Group的Licence数量不少于Customer数量，所以完全不用担心不够分配的问题。

用CTE（公共表表达式）来实现会更清晰，代码如下：

WITH CustomerRanked AS (
    -- 给同组的Customer按唯一标识（比如CustomerId）排序，生成组内序号
    SELECT 
        CustomerId,
        GroupId,
        ROW_NUMBER() OVER (PARTITION BY GroupId ORDER BY CustomerId) AS GroupRowNum
    FROM Customer
),
LicenceRanked AS (
    -- 给同组的Licence按LicenceId排序，生成组内序号
    SELECT 
        LicenceId,
        GroupId,
        ROW_NUMBER() OVER (PARTITION BY GroupId ORDER BY LicenceId) AS GroupRowNum
    FROM Licence
)
UPDATE Customer
SET LicenceId = lr.LicenceId
FROM Customer c
JOIN CustomerRanked cr ON c.CustomerId = cr.CustomerId
JOIN LicenceRanked lr ON cr.GroupId = lr.GroupId AND cr.GroupRowNum = lr.GroupRowNum;

为什么这个方案可行？ #

• 无游标，高效：完全基于集合操作，比游标循环快得多，大数据量下优势明显
• 避免多匹配：通过PARTITION BY GroupId分组，再用ROW_NUMBER()生成组内唯一序号，确保每个Customer只会匹配到同组内的一个Licence，每个Licence最多分配给一个Customer
• 灵活调整排序逻辑：如果需要按其他规则分配（比如Customer创建时间先后），只要修改ORDER BY后面的字段就行，比如换成ORDER BY CreateTime

执行前的验证建议 #

正式更新前，建议先跑个SELECT看看匹配结果是否符合预期，避免踩坑：

SELECT 
    c.CustomerId,
    c.GroupId,
    lr.LicenceId AS AssignedLicenceId
FROM Customer c
JOIN CustomerRanked cr ON c.CustomerId = cr.CustomerId
JOIN LicenceRanked lr ON cr.GroupId = lr.GroupId AND cr.GroupRowNum = lr.GroupRowNum;

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