常见SQL(生成序列号)

Test data：

Code:

select ltrim(MAX(SYS_CONNECT_BY_PATH(rn, '-')), '-') col, t2.id, t2.pid
  from (select t1.*,
               ROW_NUMBER() OVER(PARTITION BY c_level, PID ORDER BY ID) AS RN
          from (select t.id, t.pid, max(level) c_level
                  from test t
                connect by prior id = pid
                 group by t.id, t.pid) t1) t2
 start with c_level = 1
connect by prior id = pid
 group by t2.id, t2.pid, t2.rn;

Result: