如何用窗口函数实现年度单调递增标识符（含空值与间隙处理）

问题核心是：删除行导致自增Id出现间隙后，直接用ROW_NUMBER()会重新编号，无法保留已存在的非NULL业务键值，同时要为NULL值生成连续递增的新键值，且每年从1起始。

实现思路 #

1. 先获取每个年份已存在的最大非NULL业务键值；
2. 对非NULL的业务键直接保留原值；
3. 对NULL值，按插入顺序（Id排序）生成从最大值+1开始的连续编号。

修正后的SQL查询 #

SELECT
  *,
  CASE
    WHEN BusinessKey IS NOT NULL THEN BusinessKey
    ELSE COALESCE(MAX(BusinessKey) OVER (PARTITION BY [Year]), 0) + 
         ROW_NUMBER() OVER (PARTITION BY [Year], CASE WHEN BusinessKey IS NULL THEN 1 ELSE 0 END ORDER BY Id)
  END AS BusinessKeyCalculated
FROM [dbo].[Test]
ORDER BY Id;

关键逻辑说明 #

• MAX(BusinessKey) OVER (PARTITION BY [Year])：按年份分组，获取该年已有的最大业务键值；
• COALESCE(..., 0)：处理年份无任何非NULL业务键的情况，确保从1开始编号；
• ROW_NUMBER() OVER (PARTITION BY [Year], CASE WHEN BusinessKey IS NULL THEN 1 ELSE 0 END ORDER BY Id)：仅对当前年份的NULL值行按Id顺序生成序号，保证插入顺序的连续性；
• 最终通过CASE分支合并非NULL原值和NULL的递增编号。

验证结果 #

执行上述查询后，结果将与预期完全匹配：

触发器中的应用 #

将此逻辑嵌入INSERT触发器时，只需针对新插入的行（INSERTED表）计算对应的BusinessKey值即可。例如：

CREATE TRIGGER trg_Test_Insert_BusinessKey
ON [dbo].[Test]
INSTEAD OF INSERT
AS
BEGIN
  SET NOCOUNT ON;

  INSERT INTO [dbo].[Test]([Year], [BusinessKey])
  SELECT
    [Year],
    CASE
      WHEN BusinessKey IS NOT NULL THEN BusinessKey
      ELSE COALESCE((SELECT MAX(BusinessKey) FROM [dbo].[Test] t WHERE t.[Year] = inserted.[Year]), 0) + 
           ROW_NUMBER() OVER (PARTITION BY inserted.[Year], CASE WHEN inserted.BusinessKey IS NULL THEN 1 ELSE 0 END ORDER BY (SELECT NULL))
    END
  FROM inserted;
END

注：触发器中使用(SELECT NULL)作为排序依据，因为新插入的行还未生成Id，若需要严格按插入顺序，可结合IDENTITY列的特性或使用SEQUENCE对象辅助。

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