如何在SQL中创建依赖上一次迭代的循环（避免游标）

我明白你遇到的困扰了——这种需要依赖上一行计算结果的逐行更新，用WHILE TOP(1)的方式很容易因为行顺序不确定或者更新逻辑的漏洞出问题，而且效率也不高。咱们可以用**递归CTE（公共表表达式）**来实现，完全不需要游标或者那种循环更新的方式，逻辑更清晰也更可靠。

先明确你的计算逻辑 #

咱们再梳理一遍需求，确保没理解错：

• 当Index = 1时：
  • 如果Column1 >= 1，则Column2 = 1，Column3 = Column1 - 1
  • 如果Column1 < 1，则Column2 = 0，Column3 = 0
• 当Index > 1时：
  • 如果Column1 + 上一行的Column3 >= 1，则Column2 = 1，Column3 = Column1 + 上一行Column3 - 1
  • 如果Column1 + 上一行的Column3 < 1，则Column2 = 0，Column3 = 0

递归CTE的实现方案 #

递归CTE非常适合处理这种逐行依赖的计算，它分为锚点成员（处理第一行）和递归成员（处理后续行，引用上一行的结果）两部分，能严格保证按Index顺序处理每一行。

假设你的临时表结构如下

CREATE TABLE #TABLE (
    [Index] INT PRIMARY KEY, -- 这里假设Index是唯一主键，确保顺序唯一
    Column1 DECIMAL(10,2),
    Column2 INT NULL,
    Column3 DECIMAL(10,2) NULL
);

-- 插入测试数据举例
INSERT INTO #TABLE ([Index], Column1)
VALUES (1, 0.5), (2, 0.8), (3, 1.2), (4, 0.3);

核心计算代码

WITH RecursiveCalc AS (
    -- 锚点成员：处理第一行（Index=1）
    SELECT 
        [Index],
        Column1,
        CASE WHEN Column1 >= 1 THEN 1 ELSE 0 END AS Column2,
        CASE WHEN Column1 >= 1 THEN Column1 - 1 ELSE 0 END AS Column3
    FROM #TABLE
    WHERE [Index] = 1

    UNION ALL

    -- 递归成员：处理后续行，关联上一行的计算结果
    SELECT 
        t.[Index],
        t.Column1,
        CASE WHEN t.Column1 + rc.Column3 >= 1 THEN 1 ELSE 0 END AS Column2,
        CASE WHEN t.Column1 + rc.Column3 >= 1 THEN t.Column1 + rc.Column3 - 1 ELSE 0 END AS Column3
    FROM #TABLE t
    INNER JOIN RecursiveCalc rc ON t.[Index] = rc.[Index] + 1
)
-- 把计算结果更新回原临时表
UPDATE t
SET t.Column2 = rc.Column2,
    t.Column3 = rc.Column3
FROM #TABLE t
INNER JOIN RecursiveCalc rc ON t.[Index] = rc.[Index];

针对非连续Index的适配

如果你的Index不是连续的整数（比如有间隔），可以先给行编一个连续的序号，再用这个序号做递归关联：

WITH NumberedRows AS (
    -- 按原Index顺序生成连续的行号
    SELECT 
        [Index],
        Column1,
        ROW_NUMBER() OVER (ORDER BY [Index]) AS RowNum
    FROM #TABLE
),
RecursiveCalc AS (
    -- 锚点成员：处理第一行（RowNum=1）
    SELECT 
        [Index],
        Column1,
        RowNum,
        CASE WHEN Column1 >= 1 THEN 1 ELSE 0 END AS Column2,
        CASE WHEN Column1 >= 1 THEN Column1 - 1 ELSE 0 END AS Column3
    FROM NumberedRows
    WHERE RowNum = 1

    UNION ALL

    -- 递归成员：通过RowNum关联上一行
    SELECT 
        nr.[Index],
        nr.Column1,
        nr.RowNum,
        CASE WHEN nr.Column1 + rc.Column3 >= 1 THEN 1 ELSE 0 END AS Column2,
        CASE WHEN nr.Column1 + rc.Column3 >= 1 THEN nr.Column1 + rc.Column3 - 1 ELSE 0 END AS Column3
    FROM NumberedRows nr
    INNER JOIN RecursiveCalc rc ON nr.RowNum = rc.RowNum + 1
)
-- 更新原表
UPDATE t
SET t.Column2 = rc.Column2,
    t.Column3 = rc.Column3
FROM #TABLE t
INNER JOIN RecursiveCalc rc ON t.[Index] = rc.[Index];

为什么原来的WHILE方法会出错？ #

你之前用的WHILE (SELECT COUNT(*) FROM #TABLE WHERE Column2 IS NULL) > 0 + UPDATE TOP(1)的方式，最大的问题是**TOP(1)没有指定排序规则**，SQL Server无法保证每次更新的是按Index顺序的下一行，导致依赖的上一行Column3值错误，最终结果不符合预期。而递归CTE是严格按顺序处理每一行，完全避免了这个问题。

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