如何在SQL循环中选取id与revision两列共同唯一标识的下一行

问题原因 #

你现有代码的错误来自三个核心逻辑问题：

1. 初始赋值错误：首次循环前你将@id赋值为表中最大的id，后续查询很难匹配到符合条件的数据，会一直复用最后一次的有效变量值导致重复
2. 筛选条件逻辑缺陷：仅判断revision大于当前值，跨id场景下新id的revision可能远小于上一个id的最大revision，会直接漏掉这部分数据
3. 排序规则不完整：仅按id排序，同id下revision的读取顺序未做约束，会出现同id下取数顺序混乱

解决方案 #

推荐两种可直接落地的实现方式，均不会出现重复、漏数问题：

方案1：基于全局行号遍历（最稳妥，维护成本低） #

利用窗口函数给所有行按id升序、revision升序生成全局唯一行号，直接按行号遍历即可：

DECLARE @RowNum INT, @TotalRows INT, @id INT, @revision INT

-- 统计总行数
SELECT @TotalRows = COUNT(*) FROM dim.staging
SET @RowNum = 1

WHILE @RowNum <= @TotalRows
BEGIN
    -- 读取当前行号对应的唯一行
    SELECT @id = id, @revision = revision
    FROM (
        SELECT 
            id, revision,
            ROW_NUMBER() OVER(ORDER BY id ASC, revision ASC) AS rn
        FROM dim.staging
    ) AS temp
    WHERE rn = @RowNum

    -- 此处写入你的业务处理逻辑：打印、插入等操作
    
    -- 行号自增，准备读取下一行
    SET @RowNum = @RowNum + 1
END

方案2：修正现有写法的筛选逻辑 #

如果要保留你原有循环的写法，只需调整下一行的筛选条件，同时匹配id+revision的联合排序规则即可：

DECLARE @RowNum INT, @id INT, @revision INT

-- 先读取排序后的第一行作为初始值
SELECT TOP 1 @id = id, @revision = revision 
FROM dim.staging 
ORDER BY id ASC, revision ASC

SELECT @RowNum = COUNT(*) FROM dim.staging

WHILE @RowNum > 0
BEGIN
    -- 此处写入你的业务处理逻辑：打印、插入等操作
    
    -- 读取下一行：要么id比当前大，要么id相同且revision比当前大
    SELECT TOP 1 @id = id, @revision = revision 
    FROM dim.staging 
    WHERE id > @id OR (id = @id AND revision > @revision)
    ORDER BY id ASC, revision ASC

    SET @RowNum = @RowNum - 1
END

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