SQL:枚举每个组中返回的行 [英] SQL: enumerate returned rows within each group

问题描述

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

Suppose I have a SELECT ... query that returns sth. like this:

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

是否可以添加另一列来枚举每个组中的行(即，枚举管理者，工人和担任任何其他角色的人员，而与其他角色无关)?像这样:

Is it possible to add another column that enumerates rows within each group (i.e. managers, workers and people on any other role are enumerated with no regard to other roles)? Like this:

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扩展.

Usage of Oracle extensions is fine.

推荐答案

使用窗口函数:

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

这篇关于SQL:枚举每个组中返回的行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！