编写存储过程更新##FinalTable的TAB_NUM：重复或格式切换时递增

DevOps 小满

2026-8-16

问题：更新临时表TAB_NUM字段的逻辑实现问题

现有临时表结构及数据

1. ##FinalTable

存储行代码、行值及待更新的TAB_NUM，初始数据如下：

+--------------+-----+---+
| A-LINE-ONE-1 | $10 | 1 |
+--------------+-----+---+
| A-LINE-ONE-1 | $25 | 1 |
+--------------+-----+---+
| A-LINE-ONE-1 | $51 | 1 |
+--------------+-----+---+
| A-LINE-TWO-2 | $32 | 1 |
+--------------+-----+---+
| A-LINE-TWO-2 | $22 | 1 |
+--------------+-----+---+
| A-LINE-TWO-2 | $99 | 1 |
+--------------+-----+---+
| B-LINE-ONE-3 | $71 | 1 |
+--------------+-----+---+
| B-LINE-TWO-4 | $15 | 1 |
+--------------+-----+---+
| C-LINE-ONE-5 | $17 | 1 |
+--------------+-----+---+
| C-LINE-ONE-5 | $81 | 1 |
+--------------+-----+---+
| C-LINE-TWO-6 | $51 | 1 |
+--------------+-----+---+

2. ##LineFormatTable

存储行代码格式前缀，数据如下：

+----+------------------+
| No | LINE_CODE_FORMAT |
+----+------------------+
| 1  | A-LINE           |
+----+------------------+
| 2  | B-LINE           |
+----+------------------+
| 3  | C-LINE           |
+----+------------------+

需求说明

需更新##FinalTable的TAB_NUM字段，规则如下：

同一LINE_CODE的重复行，TAB_NUM逐行递增
当LINE_CODE对应的LINE_CODE_FORMAT（如从A-LINE切换到B-LINE）发生变化时，TAB_NUM需递增
期望最终结果：

+--------------+------------+---------+
| LINE_CODE    | LINE_VALUE | TAB_NUM |
+--------------+------------+---------+
| A-LINE-ONE-1 | $10        | 1       |
+--------------+------------+---------+
| A-LINE-ONE-1 | $25        | 2       |
+--------------+------------+---------+
| A-LINE-ONE-1 | $51        | 3       |
+--------------+------------+---------+
| A-LINE-TWO-2 | $32        | 1       |
+--------------+------------+---------+
| A-LINE-TWO-2 | $22        | 2       |
+--------------+------------+---------+
| A-LINE-TWO-2 | $99        | 3       |
+--------------+------------+---------+
| B-LINE-ONE-3 | $71        | 4       |
+--------------+------------+---------+
| B-LINE-TWO-4 | $15        | 4       |
+--------------+------------+---------+
| C-LINE-ONE-5 | $17        | 5       |
+--------------+------------+---------+
| C-LINE-ONE-5 | $81        | 6       |
+--------------+------------+---------+
| C-LINE-TWO-6 | $51        | 6       |
+--------------+------------+---------+

尝试的代码

以下为已编写但未正确实现逻辑的存储过程代码：

IF OBJECT_ID('tempdb..##FinalTable') IS NOT NULL
    TRUNCATE TABLE ##FinalTable
ELSE  
    CREATE TABLE ##FinalTable 
    (
        LINE_CODE varchar(20), 
        LINE_VALUE varchar(50),
        TAB_NUM int
    )
 

INSERT INTO ##FinalTable 
VALUES ('A-LINE-ONE-1', '$10', 1),
       ('A-LINE-ONE-1', '$25', 1),
       ('A-LINE-ONE-1', '$51', 1),
       ('A-LINE-TWO-2', '$32', 1),
       ('A-LINE-TWO-2', '$22', 1),
       ('A-LINE-TWO-2', '$99', 1),
       ('B-LINE-ONE-3', '$71', 1),
       ('B-LINE-TWO-4', '$15', 1),
       ('C-LINE-ONE-5', '$17', 1),
       ('C-LINE-ONE-5', '$81', 1),
       ('C-LINE-TWO-6', '$51', 1)

IF OBJECT_ID('tempdb..##LineFormatTable') IS NOT NULL
    TRUNCATE TABLE ##LineFormatTable
ELSE  
    CREATE TABLE ##LineFormatTable
    (
         No int identity(1,1),      
         LINE_CODE_FORMAT varchar(50)
    )

INSERT INTO ##LineFormatTable 
VALUES ('A-LINE'),
       ('B-LINE'),
       ('C-LINE')

DECLARE @LineFormatCount INT=(SELECT COUNT(*) FROM ##LineFormatTable)
DECLARE @TabNum INT = 0
DECLARE @i INT = 1;
DECLARE @CurrentLineNum INT=1
DECLARE @CurrentTabCode varchar(50)

WHILE @LineFormatCount >= @i
BEGIN
    SELECT @CurrentTabCode=LINE_CODE_FORMAT from ##LineFormatTable where No = @i;
    
    WITH CTE_ONE AS 
         (SELECT *,  
            ROW_NUMBER() OVER (ORDER BY LINE_CODE) AS ROW_NUMBER, 
            COUNT(*) OVER (PARTITION BY LINE_CODE) AS LINE_COUNT 
         FROM ##FinalTable)
    UPDATE CTE_ONE 
         SET CTE_ONE.TAB_NUM = CASE when ROW_NUMBER > LINE_COUNT THEN ABS(ROW_NUMBER-LINE_COUNT) + @TabNum else @TabNum  + CTE_ONE.ROW_NUMBER  end
         WHERE LINE_CODE like '%'+ @CurrentTabCode +'-%' and (LINE_COUNT > 1 or @TabNum <> CTE_ONE.TAB_NUM);

         SET @LineFormatCount =(SELECT COUNT(*) FROM ##LineFormatTable)
         SET @TabNum = (SELECT MAX(TAB_NUM) FROM ##FinalTable WHERE LINE_CODE like '%'+ @CurrentTabCode +'-%')
         select  @TabNum 
         SET @i = @i+1
END

问题分析

现有代码存在以下问题：

ROW_NUMBER() OVER (ORDER BY LINE_CODE)为全局排序，未按LINE_CODE分组排序，导致组内行号计算错误
切换LINE_CODE_FORMAT时，@TabNum的累加逻辑未正确衔接，无法实现跨FORMAT的TAB_NUM递增
CASE表达式逻辑混乱，无法同时满足组内递增和跨FORMAT递增的双重规则

修正后的解决方案

采用窗口函数一次性完成计算，避免循环带来的逻辑错误，代码如下：

-- 先确保临时表存在并插入初始数据（保留原创建逻辑）
IF OBJECT_ID('tempdb..##FinalTable') IS NOT NULL
    TRUNCATE TABLE ##FinalTable
ELSE  
    CREATE TABLE ##FinalTable 
    (
        LINE_CODE varchar(20), 
        LINE_VALUE varchar(50),
        TAB_NUM int
    )
 

INSERT INTO ##FinalTable 
VALUES ('A-LINE-ONE-1', '$10', 1),
       ('A-LINE-ONE-1', '$25', 1),
       ('A-LINE-ONE-1', '$51', 1),
       ('A-LINE-TWO-2', '$32', 1),
       ('A-LINE-TWO-2', '$22', 1),
       ('A-LINE-TWO-2', '$99', 1),
       ('B-LINE-ONE-3', '$71', 1),
       ('B-LINE-TWO-4', '$15', 1),
       ('C-LINE-ONE-5', '$17', 1),
       ('C-LINE-ONE-5', '$81', 1),
       ('C-LINE-TWO-6', '$51', 1)

IF OBJECT_ID('tempdb..##LineFormatTable') IS NOT NULL
    TRUNCATE TABLE ##LineFormatTable
ELSE  
    CREATE TABLE ##LineFormatTable
    (
         No int identity(1,1),      
         LINE_CODE_FORMAT varchar(50)
    )

INSERT INTO ##LineFormatTable 
VALUES ('A-LINE'),
       ('B-LINE'),
       ('C-LINE')

-- 核心更新逻辑
WITH LineFormatMapping AS (
    -- 匹配每个LINE_CODE对应的LINE_CODE_FORMAT及其排序
    SELECT 
        ft.*,
        lft.LINE_CODE_FORMAT,
        lft.No AS FormatOrder
    FROM ##FinalTable ft
    JOIN ##LineFormatTable lft ON ft.LINE_CODE LIKE lft.LINE_CODE_FORMAT + '-%'
),
FormatTabBase AS (
    -- 计算每个LINE_CODE_FORMAT对应的起始TAB_NUM
    SELECT 
        *,
        -- 计算当前FORMAT之前所有FORMAT的最大TAB_NUM总和
        SUM(MAX_TAB_PER_LINE) OVER (ORDER BY FormatOrder ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING) AS PrevFormatMaxTab
    FROM (
        -- 计算每个LINE_CODE的最大TAB_NUM（组内递增的最大值）
        SELECT 
            LINE_CODE_FORMAT,
            FormatOrder,
            COUNT(*) AS MAX_TAB_PER_LINE
        FROM LineFormatMapping
        GROUP BY LINE_CODE_FORMAT, FormatOrder, LINE_CODE
    ) t
),
FinalCalculation AS (
    SELECT 
        lfm.LINE_CODE,
        lfm.LINE_VALUE,
        -- 计算最终TAB_NUM：前序FORMAT的最大总和 + 组内行号（如果当前LINE_CODE有多个行）
        -- 同一FORMAT下不同LINE_CODE的起始TAB_NUM为前序总和 +1，组内递增
        CASE 
            WHEN rn > 1 THEN COALESCE(PrevFormatMaxTab, 0) + rn
            ELSE COALESCE(PrevFormatMaxTab, 0) + 1
        END AS NEW_TAB_NUM
    FROM LineFormatMapping lfm
    JOIN FormatTabBase ftb ON lfm.LINE_CODE_FORMAT = ftb.LINE_CODE_FORMAT
    -- 计算同一LINE_CODE内的行号
    CROSS APPLY (
        SELECT ROW_NUMBER() OVER (PARTITION BY lfm.LINE_CODE ORDER BY lfm.LINE_VALUE) AS rn
        FROM ##FinalTable ft_inner
        WHERE ft_inner.LINE_CODE = lfm.LINE_CODE
    ) rn_calc
)
UPDATE ft
SET ft.TAB_NUM = fc.NEW_TAB_NUM
FROM ##FinalTable ft
JOIN FinalCalculation fc ON ft.LINE_CODE = fc.LINE_CODE AND ft.LINE_VALUE = fc.LINE_VALUE;

-- 查看结果
SELECT * FROM ##FinalTable ORDER BY LINE_CODE, LINE_VALUE;

逻辑说明

LineFormatMapping：关联两张表，为每个LINE_CODE匹配对应的LINE_CODE_FORMAT和排序序号
FormatTabBase：计算每个LINE_CODE的最大行号（即组内TAB_NUM的最大值），再累加前序所有FORMAT的最大行号总和，得到当前FORMAT的起始TAB_NUM基数
FinalCalculation：计算每个行在组内的行号，结合起始基数得到最终TAB_NUM
最后通过JOIN完成批量更新，确保逻辑准确且高效

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

编写存储过程更新##FinalTable的TAB_NUM：重复或格式切换时递增

现有临时表结构及数据 #

1. ##FinalTable #

2. ##LineFormatTable #

需求说明 #

尝试的代码 #

问题分析 #

修正后的解决方案 #

逻辑说明 #