怎么保存樹狀結構的數(shù)據(jù)呢？在 SQL 中常用的是雙親節(jié)點法。創(chuàng)建表如下

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 表示父節(jié)點， name 是節(jié)點名稱。

假設當前欲獲取某一節(jié)點下所有子節(jié)點（獲取后代 Descendants），該怎么做呢？如果使用程序（Java/PHP）遞歸調用，那么將在數(shù)據(jù)庫與本地開發(fā)語言之間來回訪問，效率之低可想而知。于是我們希望在數(shù)據(jù)庫的層面就可以完成，——該怎么做呢？

遞歸法

經(jīng)查詢，最好的方法（個人覺得）是 SQL 遞歸 CTE 的方法。所謂 CTE 是 Common Table Expressison 公用表表達式的意思。網(wǎng)友評價說：“CTE 是一種十分優(yōu)雅的存在。CTE 所帶來最大的好處是代碼可讀性的提升,這是良好代碼的必須品質之一。使用遞歸 CTE 可以更加輕松愉快的用優(yōu)雅簡潔的方式實現(xiàn)復雜的查詢?！薄鋵嵨覍?SQL 不太熟悉，大家谷歌下其意思即可。

怎么用 CTE 呢？我們用小巧數(shù)據(jù)庫 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 是那個父節(jié)點之 id，你可以改為你的變量。簡單說，遞歸 CTE 最少包含兩個查詢(也被稱為成員)。第一個查詢?yōu)槎c成員，定點成員只是一個返回有效表的查詢，用于遞歸的基礎或定位點。第二個查詢被稱為遞歸成員，使該查詢稱為遞歸成員的是對 CTE 名稱的遞歸引用是觸發(fā)。在邏輯上可以將 CTE 名稱的內部應用理解為前一個查詢的結果集。遞歸查詢沒有顯式的遞歸終止條件，只有當?shù)诙€遞歸查詢返回空結果集或是超出了遞歸次數(shù)的最大限制時才停止遞歸。遞歸次數(shù)上限的方法是使用 MAXRECURION。

相應地給出查找所有父節(jié)點的方法（獲取祖先 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 語句，官網(wǎng)博客上明確說明是壓根不支持，十分不方便，明明可以很簡單事情為什么不能用呢？——而且 MySQL 也好像沒有計劃在將來的新版本中添加 with 的 cte 功能。于是大家想出了很多辦法。其實不就是一個遞歸程序么——應該不難——寫函數(shù)或者存儲過程總該行吧？沒錯，的確如此，——寫遞歸不是問題，問題是用 SQL 寫就是個問題——還是那句話，“隔行如隔山”，雖然有點夸張的說法，但我想既懂數(shù)據(jù)庫又懂各種數(shù)據(jù)庫方言寫法（存儲過程）的人應該不是很多吧~，——不細究了，反正就是代碼帖來貼去唄~

我這里就不貼 SQL 了，可以看這里的，《MySQL中進行樹狀所有子節(jié)點的查詢》

至此，我們的目的可以說已經(jīng)達到了，而且還不錯，因為這是不限層數(shù)的（以前 CMS 常說的“無限級”分類）?！鋵崳话闱闆r下，層數(shù)超過三層就很多，很復雜了，一般用戶如無特殊需求，也用不上這么多層。于是，在給定層數(shù)的約束下，可以寫標準的 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

相應地給出查找所有父節(jié)點的方法（獲取祖先 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優(yōu)化版本

但是生成的結果和第一個例子相比起來有點奇怪，而且不好給 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; 這個總算像點樣子了，結果是這樣子的。

相應地給出查找所有父節(jié)點的方法（獲取祖先 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。

其他查詢：

查詢直接子節(jié)點的總數(shù)：

SELECT c.*
,    (SELECT COUNT(*) FROM category c2 WHERE c2.parentId = c.id) 
    AS direct_children
FROM category c

•使用 with 語句遞歸，通俗易懂的例子（英文），我第一個成功的例子就是從這里 copy 的，另外還可以查層數(shù) level 和反向的父節(jié)點：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 雙親節(jié)點查找所有子節(jié)點的實現(xiàn)方法就是小編分享給大家的全部內容了，希望能給大家一個參考，也希望大家多多支持腳本之家。

最新評論