iT邦幫忙

3

CTE 遞迴卻不會有重複資料的問題

參考網路上CTE範例

資料表Employee內容如下
EmpNum EmpName Job SNum
A01 老總 總經理 0
B23 陳一哥 經理 A01
B666 蘇老大 組長 B23
C666 燈芸姊 全端工程師 B666
C52 大搖哥 全能工程師 B666

經CTE遞回跑出來的階層(JobLevel)如下
EmpNum EmpName Job SNum JobLevel
A01 老總 總經理 0 1
B23 陳一哥 經理 A01 2
B666 蘇老大 組長 B23 3
C666 燈芸姊 全端工程師 B666 4
C52 大搖哥 全能工程師 B666 4

CTE語法如下
WITH EmployeeOrder AS (
--找出老大
SELECT EmpNum, EmpName
, Job
, SNum
, 1 AS JobLevel
FROM #Employee WHERE SNum = '0'

UNION ALL

SELECT A.EmpNum, A.EmpName
	, A.Job, A.SNum
	, (B.JobLevel + 1) AS JobLevel --職位等級+1
FROM #Employee A
INNER JOIN EmployeeOrder B ON A.SNum = B.EmpNum

)
SELECT * FROM EmployeeOrder

第一步初始條件 EmployeeOrder的結果
EmpNum EmpName Job SNum JobLevel
A01 老總 總經理 0 1

遞迴第一次 EmployeeOrder的結果
EmpNum EmpName Job SNum JobLevel
A01 老總 總經理 0 1
B23 陳一哥 經理 A01 2

遞迴第二次,Employee INNER JOIN EmployeeOrder,感覺應該是類似作下面這個動作
SELECT A.EmpNum, A.EmpName, A.Job, A.SNum
FROM Employee A
INNER JOIN ( SELECT EmpNum FROM #Employee WHERE EmpNum IN ('A01', 'B23') ) B
ON A.SNum = B.EmpNum

會得到
EmpNum EmpName Job SNum
B23 陳一哥 經理 A01
B666 蘇老大 組長 B23

再跟原本的EmployeeOrder做UNION ALL的話,陳一哥的資料不是應該會出現二次嗎?

但為何最後的結果不會重複?

小魚 iT邦高手 1 級 ‧ 2018-11-10 15:52:15 檢舉
全能工程師是什麼? @@

1 個回答

4
dog830228
iT邦研究生 4 級 ‧ 2018-11-10 13:14:42
最佳解答

遞迴執行的語意如下:

  1. 將 CTE 運算式分割為錨點成員與遞迴成員。
  2. 執行錨點成員以建立第一個引動過程或基底結果集 (T0)。
  3. 執行遞迴成員,以 Ti 做為輸入,而以 Ti+1 做為輸出。
  4. 重複步驟 3,直到傳回空的結果集為止。
  5. 傳回結果集。這是 T0 至 Tn 的 UNION ALL。

查詢語法

WITH EmployeeOrder AS (
	--找出老大
	SELECT EmpNum, EmpName
	, Job
	, SNum
	, 1 AS JobLevel
	FROM #Employee WHERE SNum = '0'
	UNION ALL
	SELECT A.EmpNum, A.EmpName
		, A.Job, A.SNum
		, (B.JobLevel + 1) AS JobLevel --職位等級+1
	FROM #Employee A
	INNER JOIN EmployeeOrder B ON A.SNum = B.EmpNum
)
SELECT * FROM EmployeeOrder

依照上面描述查詢結果集順序如下

錨點結果集

A01	    老總	總經理	    0	    1

第一次結果集 因為 A.SNum = B.EmpNum ,B = A01 連結 A 表 所以得到

B23	    陳一哥	經理	    A01	    2

第二次結果集 B = B23 連結 A 表 所以得到

B666	蘇老大	組長	    B23	    3

第三次結果集 B = B666 連結 A 表 所以得到

C666	燈芸姊	全端工程師	B666	4
C52	    大搖哥	全能工程師	B666	4

所以將所有結果集 T(0) + T(1) + T(2) + T(3) 可得到

A01	    老總	總經理	    0	    1
B23	    陳一哥	經理	    A01	    2
B666	蘇老大	組長	    B23	    3
C666	燈芸姊	全端工程師	B666	4
C52	    大搖哥	全能工程師	B666	4

sqlfiddle

一般資料表運算式的遞迴查詢

我之前有分享 CTE RECURSIVE (遞迴)製作月曆 有簡單介紹原理

暐翰 iT邦大師 2 級 ‧ 2018-11-10 13:21:19 檢舉

D大,有心的詳細解答 /images/emoticon/emoticon12.gif

我要發表回答

立即登入回答