我正在練習 SQL 層次結構并使用下表：

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)

參考：

我找到了出路，我的完整代碼是：

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; 

我在“連接方式”查詢之后使用輔助列 (GrpNum) 和輔助表來實作我想要的。但這有點亂......當“連接方式”跟蹤每條路線的經理時，有沒有辦法通過每條“路線”記錄/添加列/組？

英語不是我的母語。如果我的表達不清楚，請告訴我。

uj5u.com熱心網友回復：

使用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

其中，對于樣本資料：

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;

輸出：

小提琴

標籤：sql甲骨文分层数据

上一篇：Oracle-如何按列位置而不是列名選擇列和別名列

下一篇：在OracleSQL中使用UNION運算子時如何排序。我正在使用兩個選擇陳述句和UNION運算子，我想對兩個查詢的結果進行排序