SQL使用Row_Number生成行号时跳过指定数值的问题咨询

要实现跳过指定行号的需求，只需要在原有连续行号的基础上，叠加对应跳过数量的偏移量即可。以你示例中要跳过3、4两个行号，最终得到1、2、5、6、7的结果为例，调整后的代码如下：

SELECT 
    CASE 
       WHEN clean_client_nm = 'Total Opportunities' 
          THEN 0 
          ELSE 
            CASE 
                -- 原行号大于等于3时，加2个偏移量跳过3、4
                WHEN origin_row_num >= 3 THEN origin_row_num + 2
                ELSE origin_row_num 
            END
    END AS row_id,
    prod,
    client_nm,
    clean_ft_ee,
    ISNULL(Med, ''),
    ISNULL(Stop, ''),
    ISNULL(Den, ''),
    ISNULL(VIS, ''),
    ISNULL(Life, ''),
    ISNULL(STD, ''),
    ISNULL(LTD, ''),
    ISNULL(Worksite, ''),
    client_sic
FROM 
    (
        -- 预先计算原始连续行号，避免重复调用窗口函数
        SELECT 
            *,
            ROW_NUMBER() OVER (ORDER BY clean_ft_ee DESC) AS origin_row_num
        FROM (
            SELECT * FROM TOTAL
            UNION ALL
            SELECT * FROM BASE
        ) AS X
    ) AS T
ORDER BY 
    CASE 
       WHEN clean_client_nm = 'Total Opportunities' 
          THEN 0 
          ELSE 1 
    END,
    clean_ft_ee DESC, client_nm 

如果需要跳过的数值比较零散，可以用更通用的写法，只需要维护跳过的数值列表即可，不需要手动计算偏移量：

SELECT 
    CASE 
       WHEN clean_client_nm = 'Total Opportunities' 
          THEN 0 
          ELSE origin_row_num + (
              -- 统计小于等于当前行号的跳过数值数量，自动得到偏移量
              SELECT COUNT(*) 
              FROM (VALUES(3),(4)) AS skip_list(skip_num) 
              WHERE skip_num <= origin_row_num
          )
    END AS row_id,
    -- 其余字段和排序逻辑和上文一致，无需改动
    prod,
    client_nm,
    clean_ft_ee,
    ISNULL(Med, ''),
    ISNULL(Stop, ''),
    ISNULL(Den, ''),
    ISNULL(VIS, ''),
    ISNULL(Life, ''),
    ISNULL(STD, ''),
    ISNULL(LTD, ''),
    ISNULL(Worksite, ''),
    client_sic
FROM 
    (
        SELECT 
            *,
            ROW_NUMBER() OVER (ORDER BY clean_ft_ee DESC) AS origin_row_num
        FROM (
            SELECT * FROM TOTAL
            UNION ALL
            SELECT * FROM BASE
        ) AS X
    ) AS T
ORDER BY 
    CASE 
       WHEN clean_client_nm = 'Total Opportunities' 
          THEN 0 
          ELSE 1 
    END,
    clean_ft_ee DESC, client_nm 

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