T-SQL中计算列前值获取及分组动态FINAL_TAG实现问题

一、带分组的动态标签计算（替代不符合预期的DENSE_RANK） #

如果DENSE_RANK()按OTHERCOL生成的FINAL_TAG不符合预期，通常是因为它仅基于OTHERCOL的重复值排序，无法适配分组内的自定义逻辑。以下是几种常见场景的可行实现：

1. 分组内按行序生成连续标签 #

若需在指定分组（如按GROUP_COL分组）内，按行的顺序生成从1开始的连续标签，使用ROW_NUMBER()：

SELECT 
    GROUP_COL,
    OTHERCOL,
    ROW_NUMBER() OVER (PARTITION BY GROUP_COL ORDER BY SORT_COL) AS FINAL_TAG
FROM #TEMP;

如果要求相同OTHERCOL值在分组内共享同一标签，同时保留分组内的顺序，可结合分组的DENSE_RANK()：

SELECT 
    GROUP_COL,
    OTHERCOL,
    DENSE_RANK() OVER (PARTITION BY GROUP_COL ORDER BY OTHERCOL, SORT_COL) AS FINAL_TAG
FROM #TEMP;

2. 基于分组内状态变化的动态标签 #

若标签需根据分组内的状态（如STATUS_COL）变化生成（状态改变时标签递增），用SUM() OVER()配合条件判断：

WITH CTE AS (
    SELECT 
        GROUP_COL,
        OTHERCOL,
        STATUS_COL,
        CASE WHEN STATUS_COL = LAG(STATUS_COL) OVER (PARTITION BY GROUP_COL ORDER BY SORT_COL) THEN 0 ELSE 1 END AS CHANGE_FLAG
    FROM #TEMP
)
SELECT 
    GROUP_COL,
    OTHERCOL,
    STATUS_COL,
    SUM(CHANGE_FLAG) OVER (PARTITION BY GROUP_COL ORDER BY SORT_COL) + 1 AS FINAL_TAG
FROM CTE;

3. 自定义CASE逻辑的正确用法 #

如果之前的CASE语句失效，大概率是未结合窗口函数或分组逻辑。例如在分组内根据OTHERCOL的不同值指定标签：

SELECT 
    GROUP_COL,
    OTHERCOL,
    CASE 
        WHEN OTHERCOL = 'VALUE1' THEN 1
        WHEN OTHERCOL = 'VALUE2' THEN 2
        ELSE DENSE_RANK() OVER (PARTITION BY GROUP_COL ORDER BY OTHERCOL)
    END AS FINAL_TAG
FROM #TEMP;

二、获取前一行值的实现方法 #

SQL Server的计算列无法直接引用前一行数据（计算列仅基于当前行表达式），但可通过以下方式实现类似需求：

1. 查询中用窗口函数LAG()获取前一行 #

这是最常用的方式，查询时直接获取分组内前一行的指定列值：

SELECT 
    *,
    LAG(COL_NAME) OVER (PARTITION BY GROUP_COL ORDER BY SORT_COL) AS PREV_ROW_VALUE
FROM #TEMP;

• PARTITION BY：指定分组范围，确保仅取同组内的前一行
• ORDER BY：定义行的排序规则，明确“前一行”的指向

2. 用触发器维护前一行值到列中 #

如果必须在表中持久化存储前一行值，可创建触发器实现：

-- 先添加用于存储前一行值的列
ALTER TABLE #TEMP ADD PREV_ROW_VALUE INT;

-- 创建触发器
CREATE TRIGGER TRG_TEMP_UPDATE_PREV_VALUE
ON #TEMP
AFTER INSERT, UPDATE
AS
BEGIN
    WITH ORDERED_ROWS AS (
        SELECT 
            ID, -- 假设表有唯一标识列ID
            COL_NAME,
            LAG(COL_NAME) OVER (ORDER BY SORT_COL) AS PREV_VAL
        FROM #TEMP
    )
    UPDATE t
    SET t.PREV_ROW_VALUE = o.PREV_VAL
    FROM #TEMP t
    JOIN ORDERED_ROWS o ON t.ID = o.ID;
END;

注意：临时表#TEMP的触发器会在会话结束后消失，仅适用于需持久化逻辑的永久表或当前会话内的临时表操作。

3. 用CTE递归生成前一行关联 #

对于无唯一标识的表，可通过递归CTE按顺序关联行：

WITH RECURSIVE_CTE AS (
    SELECT 
        ROW_NUMBER() OVER (ORDER BY SORT_COL) AS RN,
        *
    FROM #TEMP
    UNION ALL
    SELECT 
        c.RN,
        t.*,
        c.COL_NAME AS PREV_ROW_VALUE
    FROM RECURSIVE_CTE c
    JOIN #TEMP t ON c.RN = t.RN - 1
)
SELECT * FROM RECURSIVE_CTE;

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