I'm practicing SQL hierarcy and using a table below:

With 
EmpTable as (
      Select 7839 EMPNO,'KING' ENAME,null MGR from dual union all
      Select 7369 EMPNO,'SMITH' ENAME,7902 MGR from dual union all
      Select 7499 EMPNO,'ALLEN' ENAME,7698 MGR from dual union all
      Select 7521 EMPNO,'WARD' ENAME,7698 MGR from dual union all
      Select 7566 EMPNO,'JONES' ENAME,7839 MGR from dual union all
      Select 7654 EMPNO,'MARTIN' ENAME,7698 MGR from dual union all
      Select 7698 EMPNO,'BLAKE' ENAME,7839 MGR from dual union all
      Select 7782 EMPNO,'CLARK' ENAME,7839 MGR from dual union all
      Select 7788 EMPNO,'SCOTT' ENAME,7566 MGR from dual union all
      Select 7844 EMPNO,'TURNER' ENAME,7698 MGR from dual union all
      Select 7876 EMPNO,'ADAMS' ENAME,7788 MGR from dual union all
      Select 7900 EMPNO,'JAMES' ENAME,7698 MGR from dual union all
      Select 7902 EMPNO,'FORD' ENAME,7566 MGR from dual union all
      Select 7934 EMPNO,'MILLER' ENAME,7782 MGR from dual)

Reference:

I find a way out and my complete code is:

With 
EmpTable as (
      Select 7839 EMPNO,'KING' ENAME,null MGR from dual union all
      Select 7369 EMPNO,'SMITH' ENAME,7902 MGR from dual union all
      Select 7499 EMPNO,'ALLEN' ENAME,7698 MGR from dual union all
      Select 7521 EMPNO,'WARD' ENAME,7698 MGR from dual union all
      Select 7566 EMPNO,'JONES' ENAME,7839 MGR from dual union all
      Select 7654 EMPNO,'MARTIN' ENAME,7698 MGR from dual union all
      Select 7698 EMPNO,'BLAKE' ENAME,7839 MGR from dual union all
      Select 7782 EMPNO,'CLARK' ENAME,7839 MGR from dual union all
      Select 7788 EMPNO,'SCOTT' ENAME,7566 MGR from dual union all
      Select 7844 EMPNO,'TURNER' ENAME,7698 MGR from dual union all
      Select 7876 EMPNO,'ADAMS' ENAME,7788 MGR from dual union all
      Select 7900 EMPNO,'JAMES' ENAME,7698 MGR from dual union all
      Select 7902 EMPNO,'FORD' ENAME,7566 MGR from dual union all
      Select 7934 EMPNO,'MILLER' ENAME,7782 MGR from dual),
LevelTable as (
                Select X.*,(X.rn-X.lvl) GrpNum from 
                    (
                    Select E.*,LEVEL lvl,rownum rn from EmpTable E
                    start with E.ENAME in ('FORD','TURNER')
                    connect by Prior E.MGR= EMPNO          
                    order siblings by EMPNO
                     ) X
               )
Select LT.EMPNO,LT.ENAME,LT.MGR,LT.LVL,HelperT.Ename GrpName from LevelTable LT
left join (
           Select ENAME,GRPNUM from LevelTable
           where LVL=1
           group by ENAME,GRPNUM
          ) HelperT
on LT.GrpNUM=HelperT.GrpNUM
order by HelperT.Ename,LT.LVL; 

I use a helper column (GrpNum) and a helper table after the "connect by" query to achieve what I want. But it's a bit messy... Is there a way to take a note/add a column/group by each "route" when "connect by" is tracing its manager of each route?

English is not my mother tongue. If my expression is not clear,please let me know.

CodePudding user response：

Use CONNECT_BY_ROOT:

SELECT e.*,
       LEVEL AS lvl,
       CONNECT_BY_ROOT ename AS top_level_manager
FROM   EmpTable e
START WITH ename IN ('FORD','TURNER')
CONNECT BY PRIOR mgr = empno
ORDER SIBLINGS BY empno

Which, for the sample data:

CREATE TABLE EmpTable (empno, ename, mgr) as
  SELECT 7839, 'KING',   null FROM DUAL UNION ALL
  SELECT 7369, 'SMITH',  7902 FROM DUAL UNION ALL
  SELECT 7499, 'ALLEN',  7698 FROM DUAL UNION ALL
  SELECT 7521, 'WARD',   7698 FROM DUAL UNION ALL
  SELECT 7566, 'JONES',  7839 FROM DUAL UNION ALL
  SELECT 7654, 'MARTIN', 7698 FROM DUAL UNION ALL
  SELECT 7698, 'BLAKE',  7839 FROM DUAL UNION ALL
  SELECT 7782, 'CLARK',  7839 FROM DUAL UNION ALL
  SELECT 7788, 'SCOTT',  7566 FROM DUAL UNION ALL
  SELECT 7844, 'TURNER', 7698 FROM DUAL UNION ALL
  SELECT 7876, 'ADAMS',  7788 FROM DUAL UNION ALL
  SELECT 7900, 'JAMES',  7698 FROM DUAL UNION ALL
  SELECT 7902, 'FORD',   7566 FROM DUAL UNION ALL
  SELECT 7934, 'MILLER', 7782 FROM DUAL;

Outputs:

fiddle