SQL Server中temp表T_AMT字段更新：基于后续工作日类型的计算逻辑

表结构 #

CREATE TABLE temp
(
    T_DATE DATE, 
    T_TYPE VARCHAR(20), 
    T_AMT INT
);

示例数据 #

INSERT INTO TEMP VALUES('2020-10-19','M',230.23);
INSERT INTO TEMP VALUES('2020-10-19','P',1.23);
INSERT INTO TEMP VALUES('2020-10-20','M',130.23);
INSERT INTO TEMP VALUES('2020-10-20','P',2.23);
INSERT INTO TEMP VALUES('2020-10-21','M',23.23);
INSERT INTO TEMP VALUES('2020-10-21','S',10.23);
INSERT INTO TEMP VALUES('2020-10-22','M',220.23);
INSERT INTO TEMP VALUES('2020-10-22','T',0.23);
INSERT INTO TEMP VALUES('2020-10-23','M',830.23);
INSERT INTO TEMP VALUES('2020-10-23','P',30.23);
INSERT INTO TEMP VALUES('2020-10-26','M',230.23);
INSERT INTO TEMP VALUES('2020-10-26','P',10.23);
INSERT INTO TEMP VALUES('2020-10-27','M',230.23);
INSERT INTO TEMP VALUES('2020-10-27','S',13.23);

初始查询结果 #

执行SELECT * FROM TEMP;返回：

T_DATE      T_TYPE  T_AMT
-------------------------------
2020-10-19    M     230
2020-10-19    P       1
2020-10-20    M     130
2020-10-20    P       2
2020-10-21    M      23
2020-10-21    S      10
2020-10-22    M     220
2020-10-22    T       0
2020-10-23    M     830
2020-10-23    P      30
2020-10-26    M     230
2020-10-26    P      10
2020-10-27    M     230
2020-10-27    S      13

更新规则 #

针对所有T_TYPE = 'M'的行，按以下逻辑更新T_AMT：

• 找到当前行日期的下一个工作日（表中存在的后续最小日期）
• 检查下一个工作日对应的非M类型行的T_TYPE：
  • 若为P：当前M行的T_AMT = 前一日同组M类型的T_AMT + 下一日P类型的T_AMT
  • 若为S：当前M行的T_AMT = 前一日同组M类型的T_AMT - 下一日S类型的T_AMT
  • 若为其他类型：不做更新

示例效果 #

2020-10-19  M   230
2020-10-19  P   1
2020-10-20  M   230(前一日M值) + 2(当日P值) = 232 
2020-10-20  P   2

扩展要求 #

实际表包含T_ANO和T_CP两列，需按这两列分组，每组独立执行上述更新逻辑。

实现SQL #

WITH cte AS (
    SELECT 
        T_ANO,
        T_CP,
        T_DATE,
        T_TYPE,
        T_AMT,
        -- 获取前一日同组M类型的金额
        LAG(CASE WHEN T_TYPE = 'M' THEN T_AMT END) OVER (PARTITION BY T_ANO, T_CP ORDER BY T_DATE) AS prev_m_amt,
        -- 获取下一个工作日的非M类型及金额
        LEAD(CASE WHEN T_TYPE != 'M' THEN T_TYPE END) OVER (PARTITION BY T_ANO, T_CP ORDER BY T_DATE) AS next_day_type,
        LEAD(CASE WHEN T_TYPE != 'M' THEN T_AMT END) OVER (PARTITION BY T_ANO, T_CP ORDER BY T_DATE) AS next_day_amt
    FROM temp
)
UPDATE t
SET T_AMT = CASE
    WHEN t.T_TYPE = 'M' 
        AND c.next_day_type = 'P' 
        AND c.prev_m_amt IS NOT NULL
        THEN c.prev_m_amt + c.next_day_amt
    WHEN t.T_TYPE = 'M' 
        AND c.next_day_type = 'S' 
        AND c.prev_m_amt IS NOT NULL
        THEN c.prev_m_amt - c.next_day_amt
    ELSE t.T_AMT
END
FROM temp t
JOIN cte c 
    ON t.T_ANO = c.T_ANO 
    AND t.T_CP = c.T_CP 
    AND t.T_DATE = c.T_DATE 
    AND t.T_TYPE = c.T_TYPE;

逻辑说明 #

1. CTE窗口函数：按T_ANO、T_CP分组，日期排序，分别获取前一日M类型金额、下一个工作日的非M类型及对应金额。
2. 更新操作：关联CTE数据，仅对符合条件的M类型行执行加减更新，其余行保持原值。

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