SQL -如何标记每行和块的编号

Question

我有一个表列，如下所示

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

我想像这样为每一行和区块标注编号

(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