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;
版权声明：本文内容由互联网用户自发贡献，该文观点仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请发送邮件至 举报，一经查实，本站将立刻删除。
文章由极客之音整理，本文链接：https://www.bmabk.com/index.php/post/192758.html
