rownum是Oracle构建结果集时生成的伪列。它总是在增加。您需要的是一个row_number()函数来为每个组生成新值。由于您根据前缀分配序号，因此可以根据前缀长度和相同长度的前缀的实际值来分配行号。

with sample_data(CHILD, ROOT) as (

  select 'AA',      'A' from dual union all
  select 'AB',      'A' from dual union all
  select 'AAA',     'A' from dual union all
  select 'BB',      'B' from dual union all
  select 'BBB',     'B' from dual union all
  select 'BBBA',    'B' from dual union all
  select 'BBBB',    'B' from dual union all
  select 'C1',      'C' from dual union all
  select 'C2',      'C' from dual union all
  select 'C3',      'C' from dual union all
  select 'C4',      'C' from dual union all
  select 'C5',      'C' from dual union all
  select 'C6',      'C' from dual union all
  select 'C7',      'C' from dual union all
  select 'C8',      'C' from dual
)
select
  sample_data.*,
  row_number() over(partition by root order by lengthc(child) asc, child asc) as rn
from sample_data

| 儿童|根|RN|
| - -----|- -----|- -----|
| AA|一个|1|
| AB|一个|2|
| AAA|一个|3|
| BB| B| 1|
| 血脑屏障|B| 2|
| BBBA| B| 3|
| BBBB| B| 4|
| C1| C类|1|
| C2| C类|2|
| C3| C类|3|
| C4| C类|4|
| C5| C类|5个|
| C6| C类|六|
| C7| C类|七个|
| C8| C类|八|
fiddle