按租户ID为已有Invoices表批量填充自增invoice_number字段

要给每个租户的发票单独生成自增的invoice_number，而不是全局统一计数，你原来的全局自增代码没法区分租户，这里有两种可行的方案，优先推荐第一种高效简洁的方法：

方案一：使用窗口函数ROW_NUMBER()（推荐） #

这种方法不需要循环或游标，利用SQL的窗口函数直接按租户分组生成自增序号，效率极高，适合大数据量场景：

WITH InvoiceNumberGenerator AS (
    SELECT 
        -- 替换成你表中的唯一主键字段，确保能精准匹配每行数据
        invoice_id,
        tenant_id,
        -- 按租户分组，组内按发票的创建顺序（这里用invoice_id，你可以换成create_date等字段）生成自增序号
        ROW_NUMBER() OVER (PARTITION BY tenant_id ORDER BY invoice_id) AS new_invoice_num
    FROM Invoices
)
UPDATE target
SET invoice_number = generator.new_invoice_num
FROM Invoices target
JOIN InvoiceNumberGenerator generator 
    ON target.invoice_id = generator.invoice_id;

关键逻辑说明： #

• PARTITION BY tenant_id：把所有发票按租户拆分成独立的分组，每个租户的序号单独计算
• ORDER BY invoice_id：指定每个租户内发票的排序规则，确保序号按你需要的顺序递增（比如按发票创建时间排序更合理，换成create_date即可）
• CTE（公共表表达式）先计算出每个发票对应的目标序号，再关联原表完成批量更新

如果你的表没有单独的主键，也可以用tenant_id+其他唯一字段（比如发票创建时间+金额）组合作为关联条件，确保每行都能正确匹配。

方案二：游标循环每个租户（兼容老版本SQL） #

如果你的数据库版本不支持窗口函数（比如非常老的SQL Server版本），可以用游标遍历每个租户，再对每个租户的发票单独执行自增更新：

DECLARE @current_tenant INT;

-- 声明游标，获取所有存在发票的租户ID
DECLARE tenant_cursor CURSOR FOR
SELECT DISTINCT tenant_id FROM Invoices;

OPEN tenant_cursor;
FETCH NEXT FROM tenant_cursor INTO @current_tenant;

-- 遍历每个租户，更新其发票序号
WHILE @@FETCH_STATUS = 0
BEGIN
    DECLARE @seq INT = 0;
    
    -- 对当前租户的发票按指定顺序更新自增序号
    UPDATE Invoices
    SET @seq = invoice_number = @seq + 1
    WHERE tenant_id = @current_tenant
    ORDER BY invoice_id; -- 同样可替换为你需要的排序字段
    
    FETCH NEXT FROM tenant_cursor INTO @current_tenant;
END

-- 清理游标
CLOSE tenant_cursor;
DEALLOCATE tenant_cursor;
GO

注意事项： #

• 游标方法的效率远低于窗口函数，数据量较大时不推荐使用
• 执行前建议先备份数据，避免意外

后续新增发票的自增逻辑（触发器配合） #

批量填充完现有数据后，后续新增发票时可以用触发器自动生成对应租户的自增序号，示例如下（以SQL Server为例）：

CREATE TRIGGER trg_AssignInvoiceNumberOnInsert
ON Invoices
INSTEAD OF INSERT
AS
BEGIN
    SET NOCOUNT ON;
    
    INSERT INTO Invoices (tenant_id, invoice_number, -- 替换为你的其他字段
                         amount, create_date, ...)
    SELECT 
        i.tenant_id,
        -- 取当前租户已有的最大发票号，没有则从1开始
        ISNULL((SELECT MAX(invoice_number) FROM Invoices WHERE tenant_id = i.tenant_id), 0) + 1,
        -- 直接取插入语句中的其他字段值
        i.amount, i.create_date, ...
    FROM inserted i;
END

这个触发器会在插入新发票时，自动计算当前租户的下一个自增序号，确保每个租户的发票号连续递增。

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