MySQL8 使用WITH RECURSIVE递归查询

1.语法

WITH RECURSIVE  cte_name  AS ( 
	初始语句（非递归部分） 
	UNION ALL 
	递归部分语句
)
[ SELECT| INSERT | UPDATE | DELETE]

该语句书写包括如下几步

设定递归语法，首先初始执行第一句SELECT 1，也可以写成select xxx from xxx where xxx
其结果给到n，当n值发生改变，就会执行：SELECT n + 1 FROM cte WHERE n < 5
最终结果给到n输出

注意：WITH AS () 后面必须跟着 [ SELECT| INSERT | UPDATE | DELETE] 语句，否则报错。

2.示例

#示例 cte表名 n列名
WITH RECURSIVE cte (n) AS
(
  SELECT 1
  UNION ALL
  SELECT n + 1 FROM cte WHERE n < 5
)
SELECT * FROM cte;

3.实战

CREATE TABLE `dept`  (
  `id` int(0) NOT NULL AUTO_INCREMENT COMMENT 'id',
  `pid` int(0) NOT NULL COMMENT '父id',
  `dept_name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL COMMENT '部门',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_bin ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of dept
-- ----------------------------
INSERT INTO `dept` VALUES (1, 0, '科技公司');
INSERT INTO `dept` VALUES (2, 1, '财务部门');
INSERT INTO `dept` VALUES (3, 1, '人事部门');
INSERT INTO `dept` VALUES (4, 1, '开发部门');
INSERT INTO `dept` VALUES (5, 4, '前端部门');
INSERT INTO `dept` VALUES (6, 4, '后端部门');

SET FOREIGN_KEY_CHECKS = 1;

#查询dept.id = 4的所有父节点
WITH RECURSIVE cte (id,pid,dept_name) AS
(
  SELECT * FROM dept WHERE dept.id = 4
  UNION ALL
  SELECT dept.* FROM dept,cte WHERE dept.id = cte.pid
)
SELECT * FROM cte;

#查询dept.id = 4的所有子节点
WITH RECURSIVE cte AS (
		SELECT * FROM dept WHERE dept.id = 4
		UNION ALL
		SELECT dept.* FROM dept,cte WHERE cte.id = dept.pid
) SELECT * FROM cte;