SQL 雙親節點查找所有子節點的實現方法

投稿：jingxian 字體：[增加 減小] 類型：轉載 時間：2016-05-19 我要評論

下面小編就為大家帶來一篇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 雙親節點查找所有子節點的實現方法就是小編分享給大家的全部內容了，希望能給大家一個參考，也希望大家多多支持腳本之家。