mysql8.0版本递归查询「建议收藏」

小小码 • 2023-03-21 14:30 • 代码基础 • 阅读 149

大家好，我是考100分的小小码，祝大家学习进步，加薪顺利呀。今天说一说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‘);

根据父级Id递归查询所有子节点（查询父级Id为0 并且分类为0的所有子节点）

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

根据子节点查询所有父亲节点（查询子节点Id为8的所有父节点）

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即可当然得在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/liuxiaoji/p/11152094.html

mysql8.0版本递归查询

原文地址：https://www.cnblogs.com/atsong/p/13320754.html

版权声明：本文内容由互联网用户自发贡献，该文观点仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容，请发送邮件至举报，一经查实，本站将立刻删除。
转载请注明出处: https://daima100.com/7370.html