如何在Microsoft SQL Server中生成自定义供应商采购订单编号？

要实现格式为「年份_供应商唯一编码_年度内该供应商订单序号」的采购订单编码，可通过以下几种方案适配不同场景需求：

方案1：查询/批量更新时生成编码（非实时存储） #

如果不需要将编码持久化存储，仅在查询时动态生成，或批量更新已有订单的编码，可使用ROW_NUMBER()窗口函数按年份和供应商分组排序后拼接字符串：

批量更新已有订单编码 #

UPDATE po
SET PONumber = CONCAT(
    YEAR(po.OrderDate), '_',
    po.SupplierCode, '_',
    RIGHT('000' + CAST(ROW_NUMBER() OVER (PARTITION BY YEAR(po.OrderDate), po.SupplierCode ORDER BY po.POID) AS VARCHAR(3)), 3)
)
FROM PurchaseOrders po;

查询时实时生成编码 #

SELECT 
    POID,
    SupplierCode,
    OrderDate,
    CONCAT(
        YEAR(OrderDate), '_',
        SupplierCode, '_',
        RIGHT('000' + CAST(ROW_NUMBER() OVER (PARTITION BY YEAR(OrderDate), SupplierCode ORDER BY POID) AS VARCHAR(3)), 3)
    ) AS PONumber
FROM PurchaseOrders;

方案2：触发器自动生成插入时的编码（实时持久化） #

如果需要在插入订单时自动生成并存储编码，可创建INSTEAD OF INSERT触发器，确保编码生成后再插入数据，同时通过锁机制处理并发避免序号重复：

创建触发器 #

CREATE TRIGGER trg_GeneratePONumber
ON PurchaseOrders
INSTEAD OF INSERT
AS
BEGIN
    SET NOCOUNT ON;

    INSERT INTO PurchaseOrders (SupplierCode, OrderDate, PONumber)
    SELECT 
        i.SupplierCode,
        i.OrderDate,
        CONCAT(
            YEAR(i.OrderDate), '_',
            i.SupplierCode, '_',
            RIGHT('000' + CAST(
                ISNULL((SELECT MAX(CAST(RIGHT(PONumber, 3) AS INT)) 
                        FROM PurchaseOrders WITH (UPDLOCK, HOLDLOCK)
                        WHERE YEAR(OrderDate) = YEAR(i.OrderDate) 
                          AND SupplierCode = i.SupplierCode), 0) + 1 
            AS VARCHAR(3)), 3)
        ) AS PONumber
    FROM inserted i;
END;

关键说明 #

• WITH (UPDLOCK, HOLDLOCK)：通过锁机制锁定查询范围，避免高并发场景下多个请求同时获取相同的最大序号，导致编码重复。
• 序号位数：示例使用3位序号（如001），若需要更多位数，修改RIGHT('000'...)中0的数量即可（如5位则用RIGHT('00000'...)）。

方案3：独立序号表优化高并发场景 #

对于高并发的业务场景，建议使用独立的序号表存储每个供应商每年的当前最大序号，减少主表锁竞争：

创建序号表 #

CREATE TABLE SupplierYearSequence (
    SupplierCode VARCHAR(50) NOT NULL,
    OrderYear INT NOT NULL,
    CurrentSeq INT NOT NULL DEFAULT 1,
    PRIMARY KEY (SupplierCode, OrderYear)
);

插入订单时更新序号并生成编码 #

BEGIN TRANSACTION;

-- 获取并更新当前序号
DECLARE @currentSeq INT;
SELECT @currentSeq = CurrentSeq
FROM SupplierYearSequence WITH (UPDLOCK, HOLDLOCK)
WHERE SupplierCode = 'A' AND OrderYear = 2023;

IF @currentSeq IS NULL
BEGIN
    INSERT INTO SupplierYearSequence (SupplierCode, OrderYear, CurrentSeq)
    VALUES ('A', 2023, 1);
    SET @currentSeq = 1;
END
ELSE
BEGIN
    UPDATE SupplierYearSequence
    SET CurrentSeq = CurrentSeq + 1
    WHERE SupplierCode = 'A' AND OrderYear = 2023;
END

-- 插入订单并生成编码
INSERT INTO PurchaseOrders (SupplierCode, OrderDate, PONumber)
VALUES ('A', GETDATE(), CONCAT(2023, '_A_', RIGHT('000' + CAST(@currentSeq AS VARCHAR(3)), 3)));

COMMIT TRANSACTION;

额外注意事项 #

• 若订单的OrderDate或SupplierCode可能被修改，需添加AFTER UPDATE触发器，在字段变更时重新生成采购订单编码。
• 确保SupplierCode是供应商的唯一标识，避免因编码重复导致订单序号分组错误。

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