生活記事簿: 使用 MS SQL CTE (Common Table Expression) 的遞迴功能

MS Sql Server 有提供 CTE (Common Table Expression)，它可以讓我們用 WITH 語法來建立暫時的 Table。

例如：

WITH numRange(num) AS (
	SELECT 1
)
SELECT *
FROM numRange

查詢結果是：

而因為 CTE 可以在 AS () 中參考自已本身，
所以可以達到遞迴的效果，今天就是要來紀錄一下使用的方法。

在 AS () 中，會由兩種成員組成，分別是放在前面的錨點成員 (anchor) 和放在後面的遞迴成員 (recursive)，錨點成員是用來設定初始的 Table 內容，不可參考到 CTE 本身的 Table。

而遞迴成員會參考到 CTE Table 本身 (準確來說是參考到上一次迭代得到的資料表)。

錨點成員跟遞迴成員必須要用 UNION ALL 連接。

在每一次的遞迴迭代中，遞迴成員會參考到上一次得到的資料表，而做完 SELECT 後會產生出一份屬於這次迭代的資料表給下一次迭代的遞迴成員使用，迭代會一直進行下去直到超過次數上限 (可以用 OPTION (MAXRECURSION X) 來設定上限，X是次數，設 0 代表無限) 或是產生不出資料表為止 (就是 SELECT 的結果為空集合)。

當所有迭代執行結束後，它會把所有產生出的資料表結合起來得到最後的結果資料表。

下面舉個例子：

WITH numRange(num) AS (
	SELECT 1

	UNION ALL

	SELECT num + 1
	FROM numRange
	WHERE (num + 1) <= 3
)
SELECT *
FROM numRange

查詢結果是：

一開始初始第1次迭代的資料表是：

第2次迭代產生的資料表是

第3次迭代產生的資料表是

而第4次因為 SQL 語句 (此時 num 參考上一次、也就是第三次迭代的 num 是 3)

SELECT num + 1
	FROM numRange
	WHERE (num + 1) <= 3

得到的結果集為空，所以迭代結束。

接著下面來看另一個範例，
我們有一張表叫做 family，欄位是 name, father, mother，分別代表家族成員的名字、父親名字、母親名字，假設名字都不會重覆，所以名字可以當做 id 主鍵來做 JOIN。

表的內容如下：

name	mother	father
小孩1	媽媽	爸爸
小孩2	媽媽	爸爸
媽媽1	外婆	外公
爸爸	祖父	祖母
祖父	NULL	NULL
祖母	NULL	NULL
外公	NULL	NULL
外婆	NULL	NULL

現在我們想找出特定人的所有長輩及其的父母、還有長輩和特定人的輩份距離，就可以像下面這樣查詢，範例是要找出"小孩1"的長輩及其父母和長輩跟"小孩1"的輩份關係，相關的說明也都已寫在範例的注釋中：

-- 建立資料表
CREATE TABLE family(
   name NVARCHAR(100),
   mother NVARCHAR(100),
   father NVARCHAR(100)
);

-- 塞資料
INSERT INTO family(name, mother, father)
VALUES(N'小孩1', N'媽媽', N'爸爸'),
      (N'小孩2', N'媽媽', N'爸爸'),
	  (N'媽媽', N'外婆', N'外公'),
	  (N'爸爸', N'祖父', N'祖母'),
	  (N'祖父', NULL, NULL),
	  (N'祖母', NULL, NULL),
	  (N'外公', NULL, NULL),
	  (N'外婆', NULL, NULL);

-- 進行查詢
WITH elderRelation(elder, elder_mother, elder_father, level) AS (
   -- 這裡是錨點成員，建立一開始的初始資料集
   -- 例如此例是找出 "小孩1" 的父母及其父母和小孩與父母的輩份相差數

   -- 只要沒有參考到 elderRelation CTE 本身就是屬於錨點成員
   ---------- 錨點成員 - 開始 ----------

   -- 查詢"小孩1"的母親及其父母資料
   SELECT parent.name, parent.mother, parent.father, 1
   FROM family person INNER JOIN family parent ON person.mother = parent.name
   WHERE person.name = N'小孩1'

   UNION ALL

   -- 查詢"小孩1"的父親及其父母資料
   SELECT parent.name, parent.mother, parent.father, 1
   FROM family person INNER JOIN family parent ON person.father = parent.name
   WHERE person.name = N'小孩1'

   -- 上述只是展示錨點成員也可以自己視需要用 UNION 組合多個 Table，
   -- 上述範例也可以用一次查詢完成，例如：
   --SELECT parent.name, parent.mother, parent.father, 1
   --FROM family person INNER JOIN family parent ON person.mother = parent.name OR person.father = parent.name
   --WHERE person.name = N'小孩1'

   ---------- 錨點成員 - 結束 ----------

   -- 用 UNION ALL 與遞迴成員組合
   UNION ALL

   -- 下面因為開始參考到 elderRelation 這個 CTE 本身，所以是遞迴成員
   ---------- 遞迴成員 - 開始 ----------
   -- 找出 elderRelation 母親的父母人員資料 
   SELECT family.name, family.mother,  family.father, level + 1
   FROM elderRelation INNER JOIN family ON elderRelation.elder_mother = family.name

   UNION ALL

   -- 找出 elderRelation 父親的父母人員資料
   SELECT family.name, family.mother,  family.father, level + 1
   FROM elderRelation INNER JOIN family ON elderRelation.elder_father = family.name

   -- 上述只是展示遞迴成員也可以自己視需要用 UNION 組合多個 Table，
   -- 上述範例也可以用一次查詢完成，例如：
   --
   --SELECT family.name, family.mother,  family.father, level + 1
   --FROM elderRelation INNER JOIN family ON elderRelation.elder_mother = family.name OR elderRelation.elder_father = family.name

   ---------- 遞迴成員 - 結束 ----------
)
SELECT *
FROM elderRelation
OPTION (MAXRECURSION 0); -- 可以視需要設置遞迴的最大允許次數，0代表無限大

查詢結果如下：

elder	elder_mother	elder_father	level
媽媽	外婆	外公	1
爸爸	祖父	祖母	1
祖父	NULL	NULL	2
祖母	NULL	NULL	2
外婆	NULL	NULL	2
外公	NULL	NULL	2

參考資料：

生活記事簿

2025年1月7日星期二

使用 MS SQL CTE (Common Table Expression) 的遞迴功能

沒有留言 :

張貼留言

2025年1月7日 星期二

使用 MS SQL CTE (Common Table Expression) 的遞迴功能

沒有留言 :

張貼留言

2025年1月7日星期二