sql - Oracle - 按组生成流水号

Question

我需要在 select 语句中为一组数据生成一个运行编号/组序列。例如

Group   Name   Sequence
1       a      1
1       b      2
1       c      3
2       d      1
2       e      2
2       f      3

因此，对于每个组，序列应该是一个以 1 开头的流水号，具体取决于列“名称”的顺序。我已经用 Row_Number() 和 Level 玩了，但我找不到解决方案。知道怎么做吗？

标签： sqloracle

Answer 1

分析函数有帮助。

SQL> with test (cgroup, name) as
  2    (select 1, 'a' from dual union all
  3     select 1, 'b' from dual union all
  4     select 1, 'c' from dual union all
  5     select 2, 'd' from dual union all
  6     select 2, 'e' from dual union all
  7     select 2, 'f' from dual
  8    )
  9  select cgroup,
 10         name,
 11         row_number() over (partition by cgroup order by name) sequence
 12  from test
 13  order by cgroup, name;

    CGROUP N   SEQUENCE
---------- - ----------
         1 a          1
         1 b          2
         1 c          3
         2 d          1
         2 e          2
         2 f          3

6 rows selected.

SQL>