SQL分層查詢 - tw511教學網

　　資料中可能存在層次關係，本文章主要介紹查詢這種關係的範例。會大量使用遞迴式 CTE。

　　Emps 表中 EName 員工和 MGR 上級之間的關係如下：

　　每個上級也同樣是員工，主管和員工之間為父子關係。

1.呈現父子關係

　　情景：返回員工資料的同時返回上級資訊（名字，角色，薪水）。如下：

　　解決方案：基於 MGR 和 EName 相等自連線，找出每個員工上級的資料即可解決。

SELECT a.EName 員工名,a.MGR 上級,b.SAL 上級薪水,B.Role 上級角色 FROM test.emps a
left join test.emps b on a.MGR = b.EName

　　也可以使用標量子查詢，注意標量子查詢只能返回一列。

 select a.EName 員工名, 
 (select b.EName FROM test.emps b where b.EName = a.MGR) 上級,
  (select b.SAL FROM test.emps b where b.EName = a.MGR) 上級薪水,
   (select b.Role FROM test.emps b where b.EName = a.MGR) 上級角色
 FROM test.emps a;

2.呈現子-父-祖父關係

　　情景：員工A的上級是員工M，員工M的上級是員工B，員工B的上級是員工Z。想要呈現出這種關係。如下圖：

　　解決方案：由於要呈現的關係包含多層關係，而且是動態的，所以可以使用CTE遞回來實現。

 with RECURSIVE empss as(
    select EName,MGR,ENAme as Re from  test.emps
    union all 
    select a.EName as EName, b.MGR as MGR,CONCAT(a.Re , '->' , b.EName) as Re from empss a
    left join emps b on a.MGR = b.EName
    where a.MGR is not null
 )
 
 select EName 員工,Re 關係 from empss where MGR is null

3.建立基於表的分層檢視

　　情景：返回一個結果集，將整張表的層次結構呈現出來。在 emps 表中，員工Z上沒有上級。從員工Z 開始顯示所有下屬以及這些下屬所有的下屬。如下：

　　解決方案：該結果集相當於上一個情景反過來，區別在於從根開始。同樣這裡使用 CTE 遞迴。先找到根，然後通過 EName 和 MGR 連線尋找下屬。

with recursive empss as(
    SELECT EName as Re,EName FROM test.emps where MGR is null
    union all
    SELECT concat(b.Re,'-',a.EName) as Re,a.EName FROM test.emps a
    join empss b on a.MGR = b.EName
    
)

select Re 關係 from empss order by Re

4.確定葉子節點、分支節點和根節點

　　情景：判斷給定的行是哪種型別：葉子節點、分支節點還是根節點。在員工表中，葉子節點指的是不是任何員工上級的員工，分支節點指的是自己是上級且還是某個員工的下屬，根節點指的是沒有上級的員工。如下：

　　解決方案：使用標量子查詢在每行每個節點型別列中返回布林值。是否葉子節點，通過判斷上級是該員工的員工數量，如果是0就表示該員工是葉子節點。是否分支節點，通過判斷該員工是否存在上級，並且上級是該員工的員工數量大於0，如果大於0表示該員工是分支節點，這裡使用 sign() 函數返回標誌。是否根節點，只需要判斷該員工是否沒有上級即可。

SELECT EName,
    (select  count(*)=0 from  test.emps b where a.EName = b.MGR ) as 是否葉子節點,
    (select sign(count(*)) from  test.emps b where a.EName = b.MGR  and a.MGR is not null) as 是否分支節點,
    a.MGR is null as 是否根節點
 FROM test.emps a;