SQL - 如何为每个 ROW 和 BLOCK 标记编号

Question

我有一个类似的表列

A, A, B, C, A, A, B, D, E, E, E

我想为每个ROW&像这样的块

(A, 1), (A, 1), (B, 2), (C, 3), (A, 4), (A, 4), (B, 5), (D, 6), (E, 7), (E, 7), (E, 7)

怎么办？非常感谢。

Answer 1

假设您有一个这样的表：

SELECT * FROM t ORDER BY ord
let, ord
--------
A, 1
A, 2
B, 3
C, 4
A, 5
A, 6
B, 7
D, 8
E, 9
E, 10
E, 11

如果你这样做：

with cte as(
select let, ord, case when lag(let) over(order by ord) <> let then 1 else 0 end as letchanged
from yourtable
)
select let,
1 + sum(letchanged) over(order by ord rows unbounded preceding) as ctr
from cte

然后你会得到：

let, ctr
--------
A, 1
A, 1
B, 2
C, 3
A, 4
A, 4
B, 5
D, 6
E, 7
E, 7
E, 7