1.先在mysql數(shù)據(jù)庫添加數(shù)據(jù)

DROP TABLE IF EXISTS `dept`;

CREATE TABLE `dept`? (

`id` int(11) NOT NULL,

`pid` int(11) DEFAULT NULL,

`name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,

`date` datetime(0) DEFAULT NULL,

PRIMARY KEY (`id`) USING BTREE

) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------

-- Records of dept

-- ----------------------------

INSERT INTO `dept` VALUES (1, 0, ‘總部門‘, ‘2020-04-16 15:00:54‘);

INSERT INTO `dept` VALUES (2, 1, ‘課程部‘, ‘2020-04-16 15:00:57‘);

INSERT INTO `dept` VALUES (3, 1, ‘愛好部‘, ‘2020-04-16 15:01:01‘);

INSERT INTO `dept` VALUES (4, 1, ‘小吃部‘, ‘2020-04-16 15:01:03‘);

INSERT INTO `dept` VALUES (5, 2, ‘語文‘, ‘2020-04-15 15:01:07‘);

INSERT INTO `dept` VALUES (6, 3, ‘羽毛球‘, ‘2020-04-15 15:01:14‘);

INSERT INTO `dept` VALUES (7, 4, ‘臭豆腐‘, ‘2020-04-15 15:01:18‘);

INSERT INTO `dept` VALUES (8, 4, ‘鴨脖‘, ‘2020-07-11 11:21:29‘);

根據(jù)父級Id遞歸查詢所有子節(jié)點(查詢父級Id為0 并且分類為0的所有子節(jié)點)

WITH RECURSIVE cte AS

(

SELECT a.id, a.pid,a.name FROM dept a WHERE a.id=‘1‘

UNION ALL

SELECT k.id, k.pid,k.name FROM dept k INNER JOIN cte c ON c.id = k.pid

)SELECT id,name,pid FROM cte

根據(jù)子節(jié)點查詢所有父親節(jié)點(查詢子節(jié)點Id為8的所有父節(jié)點)

WITH RECURSIVE cte AS

(

SELECT a.id, a.pid,a.name FROM dept a WHERE a.id=‘8‘

UNION ALL

SELECT k.id, k.pid,k.name FROM dept k INNER JOIN cte c ON c.pid = k.id

)SELECT id,name,pid FROM cte

二.sqlserver 的查詢語句寫法(去掉RECURSIVE即可 當(dāng)然得在sqlserver數(shù)據(jù)庫中添加數(shù)據(jù) 并執(zhí)行語句)

WITH cte AS

(

SELECT a.id, a.pid,a.name FROM dept a WHERE a.id=‘4‘

UNION ALL

SELECT k.id, k.pid,k.name FROM dept k INNER JOIN cte c ON c.id = k.pid

)SELECT id,name,pid FROM cte

原文參考：https://www.cnblogs.com/liuxiaoji/p/11152094.html

mysql8.0版本遞歸查詢

標(biāo)簽：insert???rop???har???小吃???cte???mysq???form???sts???mysql8

本條技術(shù)文章來源于互聯(lián)網(wǎng)，如果無意侵犯您的權(quán)益請點擊此處反饋版權(quán)投訴

本文系統(tǒng)來源：https://www.cnblogs.com/atsong/p/13320754.html

總結(jié)

以上是生活随笔為你收集整理的mysql8.0递归_mysql8.0版本递归查询的全部內(nèi)容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網(wǎng)站內(nèi)容還不錯，歡迎將生活随笔推薦給好友。