SQL分層查詢 _生活百科

數據中可能存在層次關系，本文章主要介紹查詢這種關系的實例。會大量使用遞歸式 CTE 。
Emps 表中 EName 員工和 MGR 上級之間的關系如下：

文章插圖
每個上級也同樣是員工，主管和員工之間為父子關系。
1.呈現父子關系
情景：返回員工數據的同時返回上級信息（名字，角色，薪水）。如下：

文章插圖
解決方案：基于 MGR 和 EName 相等自連接，找出每個員工上級的數據即可解決。
SELECT a.EName 員工名,a.MGR 上級,b.SAL 上級薪水,B.Role 上級角色 FROM test.emps aleft 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 fromtest.empsunion allselect a.EName as EName, b.MGR as MGR,CONCAT(a.Re , '->' , b.EName) as Re from empss aleft join emps b on a.MGR = b.ENamewhere 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 nullunion allSELECT concat(b.Re,'-',a.EName) as Re,a.EName FROM test.emps ajoin empss b on a.MGR = b.EName)select Re 關系 from empss order by Re4.確定葉子節點、分支節點和根節點
情景：判斷給定的行是哪種類型：葉子節點、分支節點還是根節點。在員工表中，葉子節點指的是不是任何員工上級的員工，分支節點指的是自己是上級且還是某個員工的下屬，根節點指的是沒有上級的員工。如下：

文章插圖
解決方案：使用標量子查詢在每行每個節點類型列中返回布爾值。是否葉子節點，通過判斷上級是該員工的員工數量，如果是0就表示該員工是葉子節點。是否分支節點，通過判斷該員工是否存在上級，并且上級是該員工的員工數量大于0，如果大于0表示該員工是分支節點，這里使用 sign() 函數返回標志。是否根節點，只需要判斷該員工是否沒有上級即可。
SELECT EName,(selectcount(*)=0 fromtest.emps b where a.EName = b.MGR ) as 是否葉子節點,(select sign(count(*)) fromtest.emps b where a.EName = b.MGRand a.MGR is not null) as 是否分支節點,a.MGR is null as 是否根節點 FROM test.emps a;【SQL分層查詢】