sql - 具有两列的 T-SQL 顺序更新

Question

我有一个由以下人员创建的表:

CREATE TABLE table1 
(
    id INT,
    multiplier INT,
    col1 DECIMAL(10,5)
)

INSERT INTO table1 
VALUES (1, 2, 1.53), (2, 3, NULL), (3, 2, NULL),
       (4, 2, NULL), (5, 3, NULL), (6, 1, NULL)

结果是:

id  multiplier  col1
-----------------------
1   2           1.53000
2   3           NULL
3   2           NULL
4   2           NULL
5   3           NULL
6   1           NULL

我想添加一个定义为 multiplier * col1 的列 col2，但是 col1 的下一个值随后更新为采用col2 的先前计算值。

生成的表格应该如下所示:

id  multiplier  col1        col2
---------------------------------------
1       2         1.53000     3.06000
2       3         3.06000     9.18000
3       2         9.18000    18.36000
4       2        18.36000    36.72000
5       3        36.72000   110.16000
6       1       110.16000   110.16000

这可能使用 T-SQL 吗？我尝试了一些不同的方法，例如将 id 加入 id - 1 并尝试使用 UPDATE 和设置变量进行顺序更新但我无法让它工作。

Answer 1

递归 CTE 可能是最好的方法。假设您的 id 没有间隔:

with cte as (
      select id, multiplier, convert(float, col1) as col1, convert(float, col1 * multiplier) as col2
      from table1
      where id = 1
      union all
      select t1.id, t1.multiplier, cte.col2 as col1, cte.col2 * t1.multiplier
      from cte join
           table1 t1
           on t1.id = cte.id + 1
     )
select *
from cte;

Here是一个数据库<> fiddle 。

请注意，我将目标类型转换为float，以便于进行此类操作。如果您愿意，可以将其转换回 decimal。