mysql8.0递归_mysql8.0版本递归查询

1.先在mysql数据库添加数据

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‘);

2.语句查询id为1的所有数据

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

2.sqlserver 的查询语句写法(当然得在sqlserver数据库中添加数据 并执行语句)

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/atsong/p/13320754.html