sql server 批量循环更新同一个表里数据

Question

sql server 表里数据如下：

year（年） month（月） productID（产品ID） begamt（月初余额） inamt（本月入库） endamt（月末余额）
2022 11 1 100 20 120
2022 11 2 200 30 230

2022 12 1 0 80 0
2023 2 2 0 60 0
2023 5 2 0 70 0

需要循环更新表里的月初余额、月末余额。更新条件为同样的产品ID，用小于当前年月的最大年月的月末余额作为当前年月的月初余额
更新后的数据应为：
year（年） month（月） productID（产品ID） begamt（月初余额） inamt（本月入库） endamt（月末余额）
2022 11 1 100 20 120
2022 11 2 200 30 230

2022 12 1 120 80 200
2023 2 2 230 60 290
2023 5 2 290 70 360

为了执行速度，需要用一条sql语句更新完成，不能用循环语句，不能用游标。

谢谢！

Answer 1

重点在于从分区第一条数据开始计算对吧，然后就是更新，没得SqlServer以mysql为例吧， 思路是分区递归计算

CREATE TABLE t_example (
    f_year INT NULL,
    f_month INT NULL,
    f_product_id INT NULL,
    begamt INT NULL,
    inamt INT NULL,
    endamt INT NULL
)
ENGINE=InnoDB
DEFAULT CHARSET=utf8mb4
COLLATE=utf8mb4_0900_ai_ci;

insert into t_example values(2022, 11, 1, 100, 20, 120);
insert into t_example values(2022, 11, 2, 200, 30, 230);
insert into t_example values(2022, 12, 1, 0, 80, 0);
insert into t_example values(2023, 2, 2, 0, 60, 0);
insert into t_example values(2023, 5, 2, 0, 70, 0);

with recursive CTE_PART as (
select *, ROW_NUMBER() over(partition by f_product_id order by f_year, f_month) as rn from t_example
), CTE_RECU as (
select p.f_year, p.f_month, p.f_product_id, p.begamt, p.inamt, (p.begamt + p.inamt) as endamt, p.rn from CTE_PART p where p.rn = 1 union ALL 
select p1.f_year, p1.f_month, p1.f_product_id, c.endamt, p1.inamt, (c.endamt + p1.inamt) as endamt, p1.rn from CTE_PART p1, CTE_RECU c
where p1.f_product_id = c.f_product_id and p1.rn = (c.rn +1)
) update CTE_RECU c inner join t_example t 
on c.f_product_id = t.f_product_id and c.f_year = t.f_year and c.f_month = t.f_month
set t.begamt = c.begamt, t.inamt = c.inamt, t.endamt = c.endamt;