怎么保存樹(shù)狀結(jié)構(gòu)的數(shù)據(jù)呢？在 SQL 中常用的是雙親節(jié)點(diǎn)法。創(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é)點(diǎn)， name 是節(jié)點(diǎn)名稱(chēng)。

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

遞歸法

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

怎么用 CTE 呢？我們用小巧數(shù)據(jù)庫(kù) SQLite，它就支持！別看他體積不大，卻也能支持最新 SQL99 的 with 語(yǔ)句，例子如下。

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

相應(yīng)地給出查找所有父節(jié)點(diǎn)的方法（獲取祖先 Ancestors，就是把 id 和 parentId 反過(guò)來(lái)）

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; 

無(wú)奈的 MySQL

SQLite ok 了，而 MySQL 呢？

在另一邊廂，大家都愛(ài)用的 MySQL 卻無(wú)視 with 語(yǔ)句，官網(wǎng)博客上明確說(shuō)明是壓根不支持，十分不方便，明明可以很簡(jiǎn)單事情為什么不能用呢？——而且 MySQL 也好像沒(méi)有計(jì)劃在將來(lái)的新版本中添加 with 的 cte 功能。于是大家想出了很多辦法。其實(shí)不就是一個(gè)遞歸程序么——應(yīng)該不難——寫(xiě)函數(shù)或者存儲(chǔ)過(guò)程總該行吧？沒(méi)錯(cuò)，的確如此，——寫(xiě)遞歸不是問(wèn)題，問(wèn)題是用 SQL 寫(xiě)就是個(gè)問(wèn)題——還是那句話(huà)，“隔行如隔山”，雖然有點(diǎn)夸張的說(shuō)法，但我想既懂?dāng)?shù)據(jù)庫(kù)又懂各種數(shù)據(jù)庫(kù)方言寫(xiě)法（存儲(chǔ)過(guò)程）的人應(yīng)該不是很多吧~，——不細(xì)究了，反正就是代碼帖來(lái)貼去唄~

我這里就不貼 SQL 了，可以看這里的，《MySQL中進(jìn)行樹(shù)狀所有子節(jié)點(diǎn)的查詢(xún)》

至此，我們的目的可以說(shuō)已經(jīng)達(dá)到了，而且還不錯(cuò)，因?yàn)檫@是不限層數(shù)的（以前 CMS 常說(shuō)的“無(wú)限級(jí)”分類(lèi)）。——其實(shí)，一般情況下，層數(shù)超過(guò)三層就很多，很復(fù)雜了，一般用戶(hù)如無(wú)特殊需求，也用不上這么多層。于是，在給定層數(shù)的約束下，可以寫(xiě)標(biāo)準(zhǔn)的 SQL 來(lái)完成該任務(wù)——盡管有點(diǎn)寫(xiě)死的感覺(jué)~~

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

相應(yīng)地給出查找所有父節(jié)點(diǎn)的方法（獲取祖先 Ancestors，就是把 id 和 parentId 反過(guò)來(lái)）

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)化版本

但是生成的結(jié)果和第一個(gè)例子相比起來(lái)有點(diǎn)奇怪，而且不好給 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; 這個(gè)總算像點(diǎn)樣子了，結(jié)果是這樣子的。

相應(yīng)地給出查找所有父節(jié)點(diǎn)的方法（獲取祖先 Ancestors，就是把 id 和 parentId 反過(guò)來(lái)， 還有改改 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; 

這樣就很通用啦~無(wú)論你 SQLite 還是 MySQL。

其他查詢(xún)：

查詢(xún)直接子節(jié)點(diǎn)的總數(shù)：

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

•使用 with 語(yǔ)句遞歸，通俗易懂的例子（英文），我第一個(gè)成功的例子就是從這里 copy 的，另外還可以查層數(shù) level 和反向的父節(jié)點(diǎn)：https://www.valentina-db.com/dokuwiki/doku.php?id=valentina:articles:recursive_query

•標(biāo)準(zhǔn)寫(xiě)法的出處（英文）：http://stackoverflow.com/questions/20215744/how-to-create-a-mysql-hierarchical-recursive-query

•很好的總結(jié)貼（英文）：http://mikehillyer.com/articles/managing-hierarchical-data-in-mysql/

•SQlite with 語(yǔ)句用法中文翻譯（太晦澀，不懂鳥(niǎo)） http://blog.csdn.net/aflyeaglenku/article/details/50978986

•利用閉包做的樹(shù)結(jié)構(gòu)（書(shū)上說(shuō)這個(gè)方法最好，但同時(shí)覺(jué)得也很高級(jí)，英文）http://charlesleifer.com/blog/querying-tree-structures-in-sqlite-using-python-and-the-transitive-closure-extension/

以上這篇SQL 雙親節(jié)點(diǎn)查找所有子節(jié)點(diǎn)的實(shí)現(xiàn)方法就是小編分享給大家的全部?jī)?nèi)容了，希望能給大家一個(gè)參考，也希望大家多多支持腳本之家。