sql - 如何获取每个空值的行号？

Question

我需要按顺序获取每条 null 记录的行号。获取行中的值时重新开始编号。

我已经试过了

select *
     , ROW_NUMBER() over (order by id) rn
from @tbl 

select *
     , ROW_NUMBER() over (partition by value order by id) rn
from @tbl 

declare @tbl table(id int, value int)
insert into @tbl values 
(1, null), (2, null), (3, null), (4, 1),(5, null), (6, null), (7, 1), (8, null), (9, null), (10, null)

select *
     , ROW_NUMBER() over (partition by value order by id) rn
from @tbl 

我明白了:

id, value,  rn
1   NULL    1
2   NULL    2
3   NULL    3
4   1       4
5   NULL    5
6   NULL    6
7   1       7
8   NULL    8
9   NULL    9
10  NULL    10

我想要这样的结果

id, value,  rn
1   NULL    1
2   NULL    2
3   NULL    3
4   1       1
5   NULL    1
6   NULL    2
7   1       1
8   NULL    1
9   NULL    2
10  NULL    3

如何使用 sql 查询获得所需的结果？

Answer 1

此方法使用 COUNT 作为 value 列的分析函数，为每个 NULL 值 block 生成“组”。要查看其工作原理，只需使用以下代码运行 SELECT * FROM cte。然后，使用这个计算组，我们使用 ROW_NUMBER 生成 NULL 值的序列。我们按值升序排列，这意味着每个 NULL 行号序列将始终以 1 开头，这是我们想要的行为。对于具有非 NULL 值的记录，我们只需将该值拉入 rn 列。

WITH cte AS (
    SELECT *, COUNT(value) OVER (ORDER BY id) vals
    FROM @tbl
)

SELECT id, value,
    CASE WHEN value IS NULL
         THEN ROW_NUMBER() OVER (PARTITION BY vals ORDER BY value)
         ELSE value END AS rn
FROM cte
ORDER BY id;

Demo