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

Question

假设我有一个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扩展很好。

Answer 1

使用窗口功能：

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