對自引用表的遞歸查詢，其中每個節點都有一個指向其子節點的連結

考慮下表和數據（此處提供小提琴）：

CREATE TABLE test 
(
 id INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY,
 next_id INTEGER NOT NULL
);

INSERT INTO test (id,   next_id) VALUES
(1, 3),
(2, 3),
(3, 6),
(4, 5),
(5, 6),
(6, 8),
(7, 10),
(8, 9);

我希望能夠以遞歸方式僅選擇從 ID = 1 開始的行，跟隨 Next ID。也就是說，我想在此範例中選擇以下行：

我意識到使用程式語言實現它會相對容易，但我想知道是否有人知道如何使用純 SQL 來實現。

您可以執行以下命令（下面的所有程式碼都可以在此處的小提琴中找到）：

WITH RECURSIVE cte (id_, next_id_) AS
(
 SELECT id, next_id FROM test WHERE id = (SELECT MIN(id) FROM test)
 UNION ALL
 SELECT 
   c.next_id_, t.next_id
 FROM
   cte c
 JOIN test t
 ON c.next_id_ = t.id
)
SELECT * FROM cte;

結果：

rn  id_   next_id_
1    1          3
2    3          6
3    6          8
4    8          9

這些查詢很棘手，可能需要一個人（好吧，無論如何我）一段時間才能理解它們，所以我將逐行瀏覽這一行 - 對我和你一樣！:-) （或其他任何與這些精緻且（可能非常）複雜的野獸作鬥爭的人！）。

所以，第一行

WITH RECURSIVE cte (id_, next_id_) AS

該RECURSIVE關鍵字對於 MySQL、MariaDB和PostgreSQL是必需的，對於SQL Server、SQLite和Oracle會引發錯誤。您可能需要也可能不需要括號中的欄位定義 - 自己檢查一下 - 大多數人似乎都接受它，這在您實際制定查詢時會有所幫助！

然後：

SELECT id, next_id FROM test WHERE id = (SELECT MIN(id) FROM test)

我們的“種子”或“錨”查詢——我們的第一個值RECURSIVE CTE——在這種情況下，它是元組(1, 3)。

緊隨UNION ALL其後的是查詢的遞歸部分的核心：

SELECT 
 c.next_id_, t.next_id
FROM
 cte c
JOIN test t
ON c.next_id_ = t.id

因此，從 開始(1,3)，我們從SELECT3 ( c.next_id)和從它們中獲取RCTE一個值- 現在是測試，因此我們獲得了元組。test``JOIN``ON 3 = t.id``t.id``PK``UNIQUE``(3, 6)

在下一次迭代中，我們將6在- 中cte.id_獲取JOIN值cte.next_id_，以此類推到表的末尾 -然後獲取並終止查詢。瞧——想要的結果！8``JOIN``8``9

@RickJames 好心地指出，可用的遞歸深度是有限制的。在 MySQL 中，這是由系統變數確定的cte_max_recursion_depth，預設情況下等於 1000（參見小提琴底部）。

SHOW VARIABLES LIKE 'cte_max_recursion_depth';

結果：

Variable_name            Value
cte_max_recursion_depth   1000

它可以在會話或全域基礎上設置。

SET cte_max_recursion_depth = 1200;    ✓

重新執行我們的SHOW VARIABLES命令：

SHOW VARIABLES LIKE 'cte_max_recursion_depth';

結果：

        Variable_name   Value
cte_max_recursion_depth   1200

當然，它不能無限增加（甚至不能增加到最大值 4294967295）——系統只有有限的資源——YMMV！

引用自：https://dba.stackexchange.com/questions/301903