Oracle 分层查询中重复父节点

你好AskTOM团队，

使用LiveSQL链接中提供的架构 (这只是EMP/DEPT架构的示例)，我运行了以下查询

 select case 
          when LEVEL = 1 then 
            ENAME 
          else 
            rpad('|  ', (LEVEL - 2) * 3, '|  ') || '|--' || ENAME 
        end ENAME 
   from EMP 
connect by prior EMPNO = MGR 
  start with MGR is null 
  order siblings by EMPNO

要获得此输出

KING
|--JONES
|  |--SCOTT
|  |  |--ADAMS
|  |--FORD
|  |  |--SMITH
|--BLAKE
|  |--ALLEN
|  |--WARD
|  |--MARTIN
|  |--TURNER
|  |--JAMES
|--CLARK
|  |--MILLER

现在，我想要的是每次有新的叶子时要重复的父节点。所以它基本上应该是这样的

KING
|--JONES
|  |--SCOTT
|  |  |--ADAMS
KING
|--JONES
|  |--FORD
|  |  |--SMITH
KING
|--BLAKE
|  |--ALLEN
KING
|--BLAKE
|  |--WARD
KING
|--BLAKE
|  |--MARTIN
KING
|--BLAKE
|  |--TURNER
KING
|--BLAKE
|  |--JAMES
KING
|--CLARK
|  |--MILLER

有没有办法做到这一点？我玩了connect_by_root和connect_by_isleaf，但无法反映我想要的结果。

提前感谢您抽出宝贵的时间回答这个问题!

这是一种方法:

-使用sys_connect_by_path将从根到每行的路径构建为CSV
-使用connect_by_isleaf识别叶子
-然后对于每个叶子，使用您喜欢的 “CSV到行” 方法为层次结构中的每个节点生成一行

例如:

with rws as (
 select case 
          when LEVEL = 1 then 
            ENAME 
          else 
            rpad('|  ', (LEVEL - 2) * 3, '|  ') || '|--' || ENAME 
        end ENAME,
        sys_connect_by_path(ename, ',') pth,
        connect_by_isleaf lf
   from scott.EMP 
connect by prior EMPNO = MGR 
  start with MGR is null 
  order siblings by EMPNO
), leaves as (
  select * from rws
  where  lf = 1
)
  select pth, rpad('|  ', (l ) * 3, '|  ') || regexp_substr(pth, '[^,]+', 1, l) nm
  from   leaves, lateral (
    select level l
    from   dual
    connect by level <= length(regexp_replace(pth, '[^,]'))
  )
  order by pth, l;

PTH                       NM                  
,KING,BLAKE,ALLEN         |  KING             
,KING,BLAKE,ALLEN         |  |  BLAKE         
,KING,BLAKE,ALLEN         |  |  |  ALLEN      
,KING,BLAKE,JAMES         |  KING             
,KING,BLAKE,JAMES         |  |  BLAKE         
,KING,BLAKE,JAMES         |  |  |  JAMES      
,KING,BLAKE,MARTIN        |  KING             
,KING,BLAKE,MARTIN        |  |  BLAKE         
,KING,BLAKE,MARTIN        |  |  |  MARTIN     
,KING,BLAKE,TURNER        |  KING             
,KING,BLAKE,TURNER        |  |  BLAKE         
,KING,BLAKE,TURNER        |  |  |  TURNER     
,KING,BLAKE,WARD          |  KING             
,KING,BLAKE,WARD          |  |  BLAKE         
,KING,BLAKE,WARD          |  |  |  WARD       
,KING,CLARK,MILLER        |  KING             
,KING,CLARK,MILLER        |  |  CLARK         
,KING,CLARK,MILLER        |  |  |  MILLER     
,KING,JONES,FORD,SMITH    |  KING             
,KING,JONES,FORD,SMITH    |  |  JONES         
,KING,JONES,FORD,SMITH    |  |  |  FORD       
,KING,JONES,FORD,SMITH    |  |  |  |  SMITH   
,KING,JONES,SCOTT,ADAMS   |  KING             
,KING,JONES,SCOTT,ADAMS   |  |  JONES         
,KING,JONES,SCOTT,ADAMS   |  |  |  SCOTT      
,KING,JONES,SCOTT,ADAMS   |  |  |  |  ADAMS