SQL 中怎么利用雙親節(jié)點(diǎn)查找所有子節(jié)點(diǎn)

這期內(nèi)容當(dāng)中小編將會(huì)給大家?guī)?lái)有關(guān)SQL 中怎么利用雙親節(jié)點(diǎn)查找所有子節(jié)點(diǎn)，文章內(nèi)容豐富且以專業(yè)的角度為大家分析和敘述，閱讀完這篇文章希望大家可以有所收獲。

創(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, 'Branch2' )INSERT INTO category VALUES ( 3, 1, 'Branch3' )INSERT INTO category VALUES ( 4, 3, 'SubBranch2' )INSERT INTO category VALUES ( 5, 2, 'SubBranch3' )

其中，parent id 表示父節(jié)點(diǎn)， name 是節(jié)點(diǎn)名稱。

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

遞歸法

經(jīng)查詢，最好的方法（個(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ù)雜的查詢。”——其實(shí)我對(duì) SQL 不太熟悉，大家谷歌下其意思即可。

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

WITH w1( id, parentId, name) AS (SELECT category.id, category.parentId,             category.nameFROM category WHERE id = 1UNION ALL SELECT category.id, category.parentId,             category.nameFROM category JOIN w1 ON category.parentId= w1.id)

SELECT * FROM w1;其中 WHERE id = 1 是那個(gè)父節(jié)點(diǎn)之 id，你可以改為你的變量。簡(jiǎn)單說(shuō)，遞歸 CTE 最少包含兩個(gè)查詢(也被稱為成員)。第一個(gè)查詢?yōu)槎c(diǎn)成員，定點(diǎn)成員只是一個(gè)返回有效表的查詢，用于遞歸的基礎(chǔ)或定位點(diǎn)。第二個(gè)查詢被稱為遞歸成員，使該查詢稱為遞歸成員的是對(duì) CTE 名稱的遞歸引用是觸發(fā)。在邏輯上可以將 CTE 名稱的內(nèi)部應(yīng)用理解為前一個(gè)查詢的結(jié)果集。遞歸查詢沒(méi)有顯式的遞歸終止條件，只有當(dāng)?shù)诙€(gè)遞歸查詢返回空結(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)該不難——寫函數(shù)或者存儲(chǔ)過(guò)程總該行吧？沒(méi)錯(cuò)，的確如此，——寫遞歸不是問(wèn)題，問(wèn)題是用 SQL 寫就是個(gè)問(wèn)題——還是那句話，“隔行如隔山”，雖然有點(diǎn)夸張的說(shuō)法，但我想既懂?dāng)?shù)據(jù)庫(kù)又懂各種數(shù)據(jù)庫(kù)方言寫法（存儲(chǔ)過(guò)程）的人應(yīng)該不是很多吧~，——不細(xì)究了，反正就是代碼帖來(lái)貼去唄~

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

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

SELECT t1.name AS lev1, t2.name as lev2, t3.name as lev3, t4.name as lev4FROM category AS t1LEFT JOIN category AS t2 ON t2.parentId = t1.idLEFT JOIN category AS t3 ON t3.parentId = t2.idLEFT JOIN category AS t4 ON t4.parentId = t3.idWHERE 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_idFROMcategory p1LEFT 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.parentIdWHERE 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。

其他查詢：

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

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

上述就是小編為大家分享的SQL 中怎么利用雙親節(jié)點(diǎn)查找所有子節(jié)點(diǎn)了，如果剛好有類似的疑惑，不妨參照上述分析進(jìn)行理解。如果想知道更多相關(guān)知識(shí)，歡迎關(guān)注億速云行業(yè)資訊頻道。