一尘不染

SQL:枚举每个组中返回的行

sql

假设我有一个SELECT ...返回sth的查询。像这样:

role      name
--------  -------
MANAGER   Alice
WORKER    Bob
WORKER    Evan
WORKER    John
MANAGER   Max
WORKER    Steve

是否可以添加另一列来枚举每个组中的行(即,枚举具有其他角色的经理,工人和人员,而不考虑其他角色)?像这样:

role      name     no.
--------  -------  ----
MANAGER   Alice     1
WORKER    Bob       1        // second row, but the first worker
WORKER    Evan      2
WORKER    John      3
MANAGER   Max       2        // fifth row, but only the second manager
WORKER    Steve     4

使用Oracle扩展很好。


阅读 193

收藏
2021-03-08

共1个答案

一尘不染

使用窗口功能:

select role, name, 
       row_number() over (partition by role order by name) as rn
from the_table
order by name;
2021-03-08