SQL 雙親節點查找一切子節點的完成辦法。本站提示廣大學習愛好者：（SQL 雙親節點查找一切子節點的完成辦法）文章只能為提供參考，不一定能成為您想要的結果。以下是SQL 雙親節點查找一切子節點的完成辦法正文
怎樣保留樹狀構造的數據呢？在 SQL 中經常使用的是雙親節點法。創立表以下
CREATE TABLE category ( id LONG, parentId LONG, name String(20) ) INSERT INTO category VALUES ( 1, NULL, 'Root' ) INSERT INTO category VALUES ( 2, 1, 'Branch1' ) INSERT INTO category VALUES ( 3, 1, 'Branch2' ) INSERT INTO category VALUES ( 4, 3, 'SubBranch1' ) INSERT INTO category VALUES ( 5, 2, 'SubBranch2' )
個中，parent id 表現父節點， name 是節點稱號。
假定以後欲獲得某一節點下一切子節點（獲得子女 Descendants），該怎樣做呢？假如應用法式（Java/PHP）遞歸挪用，那末將在數據庫與當地開辟說話之間往返拜訪，效力之低可想而知。因而我們願望在數據庫的層面便可以完成，——該怎樣做呢？
遞歸法
經查詢，最好的辦法（小我認為）是 SQL 遞歸 CTE 的辦法。所謂 CTE 是 Common Table Expressison 公用表表達式的意思。網友評價說：“CTE 是一種非常優雅的存在。CTE 所帶來最年夜的利益是代碼可讀性的晉升,這是優越代碼的必需品德之一。應用遞歸 CTE 可以加倍輕松高興的用優雅簡練的方法完成龐雜的查詢。”——其實我對 SQL 不太熟習，年夜家谷歌下其意思便可。
怎樣用 CTE 呢？我們用玲珑數據庫 SQLite，它就支撐！別看他體積不年夜，卻也能支撐最新 SQL99 的 with 語句，例子以下。
WITH w1( id, parentId, name) AS
( SELECT
category.id,
category.parentId,
category.name
FROM
category
WHERE
id = 1
UNION ALL
SELECT
category.id,
category.parentId,
category.name
FROM
category JOIN w1 ON category.parentId= w1.id
)
SELECT * FROM w1;個中 WHERE id = 1 是誰人父節點之 id，你可以改成你的變量。簡略說，遞歸 CTE 起碼包括兩個查詢(也被稱為成員)。第一個查詢為定點成員，定點成員只是一個前往有用表的查詢，用於遞歸的基本或定位點。第二個查詢被稱為遞歸成員，使該查詢稱為遞歸成員的是對 CTE 稱號的遞歸援用是觸發。在邏輯上可以將 CTE 稱號的外部運用懂得為前一個查詢的成果集。遞歸查詢沒有顯式的遞歸終止前提，只要當第二個遞歸查詢前往空成果集或是超越了遞歸次數的最年夜限制時才停滯遞歸。遞歸次數下限的辦法是應用 MAXRECURION。
響應地給出查找一切父節點的辦法（獲得先人 Ancestors，就是把 id 和 parentId 反過去）
WITH w1( id, parentId, name, level) AS
( SELECT
id,
parentId,
name,
0 AS level
FROM
category
WHERE
id = 6
UNION ALL
SELECT
category.id,
category.parentId,
category.name ,
level + 1
FROM
category JOIN w1 ON category.id= w1.parentId
)
SELECT * FROM w1;
無法的 MySQL
SQLite ok 了，而 MySQL 呢？
在另外一邊廂，年夜家都愛用的 MySQL 卻疏忽 with 語句，官網博客上明白解釋是壓根不支撐，非常不便利，明明可以很簡略工作為何不克不及用呢？——並且 MySQL 也似乎沒有籌劃在未來的新版本中添加 with 的 cte 功效。因而年夜家想出了許多方法。其實不就是一個遞歸途序麼——應當不難——寫函數或許存儲進程總該行吧？沒錯，切實其實如斯，——寫遞歸不是成績，成績是用 SQL 寫就是個成績——照樣那句話，“隔行如隔山”，固然有點誇大的說法，但我想既懂數據庫又懂各類數據庫方言寫法（存儲進程）的人應當不是許多吧~，——不細究了，橫豎就是代碼帖來貼去呗~
我這裡就不貼 SQL 了，可以看這裡的，《MySQL中停止樹狀一切子節點的查詢》
至此，我們的目標可以說曾經到達了，並且還不錯，由於這是不限層數的（之前 CMS 常說的“無窮級”分類）。——其實，普通情形下，層數跨越三層就許多，很龐雜了，普通用戶如無特別需求，也用不上這麼多層。因而，在給定層數的束縛下，可以寫尺度的 SQL 來完成該義務——雖然有點寫逝世的感到~~
SELECT t1.name AS lev1, t2.name as lev2, t3.name as lev3, t4.name as lev4 FROM category AS t1 LEFT JOIN category AS t2 ON t2.parentId = t1.id LEFT JOIN category AS t3 ON t3.parentId = t2.id LEFT JOIN category AS t4 ON t4.parentId = t3.id WHERE t1.id= 1
響應地給出查找一切父節點的辦法（獲得先人 Ancestors，就是把 id 和 parentId 反過去）
SELECT t1.name AS lev1, t2.name as lev2, t3.name as lev3, t4.name as lev4
FROM category AS t1
LEFT JOIN category AS t2 ON t2.id= t1.parentId
LEFT JOIN category AS t3 ON t3.id= t2.parentId
LEFT JOIN category AS t4 ON t4.id= t3.parentId
WHERE t1.id= 10優化版本
然則生成的成果和第一個例子比擬起來有點奇異，並且欠好給 Java 用，——那就再找找其他例子
SELECT
p1.id,
p1.name,
p1.parentId as parentId,
p2.parentId as parent2_id,
p3.parentId as parent3_id,
p4.parentId as parent4_id,
p5.parentId as parent5_id,
p6.parentId as parent6_id
FROM category p1
LEFT JOIN category p2 on p2.id = p1.parentId
LEFT JOIN category p3 on p3.id = p2.parentId
LEFT JOIN category p4 on p4.id = p3.parentId
LEFT JOIN category p5 on p5.id = p4.parentId
LEFT JOIN category p6 on p6.id = p5.parentId
WHERE 1 IN (p1.parentId,
p2.parentId,
p3.parentId,
p4.parentId,
p5.parentId,
p6.parentId)
ORDER BY 1, 2, 3, 4, 5, 6, 7; 這個總算像點模樣了，成果是如許子的。
響應地給出查找一切父節點的辦法（獲得先人 Ancestors，就是把 id 和 parentId 反過去， 還有改改 IN 外面的字段名）
SELECT
p1.id,
p1.name,
p1.parentId as parentId,
p2.parentId as parent2_id,
p3.parentId as parent3_id
FROM category p1
LEFT JOIN category p2 on p2.parentId = p1.id
LEFT JOIN category p3 on p3.parentId = p2.id
WHERE 9 IN (p1.id,
p2.id,
p3.id)
ORDER BY 1, 2, 3;
如許就很通用啦~不管你 SQLite 照樣 MySQL。
其他查詢：
查詢直接子節點的總數：
SELECT c.*
, (SELECT COUNT(*) FROM category c2 WHERE c2.parentId = c.id)
AS direct_children
FROM category c
•應用 with 語句遞歸，淺顯易懂的例子（英文），我第一個勝利的例子就是從這裡 copy 的，別的還可以查層數 level 和反向的父節點：https://www.valentina-db.com/dokuwiki/doku.php?id=valentina:articles:recursive_query
•尺度寫法的出處（英文）：http://stackoverflow.com/questions/20215744/how-to-create-a-mysql-hierarchical-recursive-query
•很好的總結貼（英文）：http://mikehillyer.com/articles/managing-hierarchical-data-in-mysql/
•SQlite with 語句用法中文翻譯（太艱澀，不懂鳥） http://blog.csdn.net/aflyeaglenku/article/details/50978986
•應用閉包做的樹構造（書上說這個辦法最好，但同時認為也很高等，英文）http://charlesleifer.com/blog/querying-tree-structures-in-sqlite-using-python-and-the-transitive-closure-extension/
以上這篇SQL 雙親節點查找一切子節點的完成辦法就是小編分享給年夜家的全體內容了，願望能給年夜家一個參考，也願望年夜家多多支撐。
