环境
需要MySQL8.0+
上下级关系图
![图片[1]-【MySQL】递归查询上下级-小樱博客](https://oss.kvmao.com/2022/06/20220630055901564.png?imageView2/0/format/webp/q/75)
DROP TABLE IF EXISTS `t_dept`;
CREATE TABLE `t_dept` (
`id` int(10) NOT NULL,
`pid` int(10) NULL DEFAULT NULL,
`name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;
INSERT INTO `t_dept` VALUES (0, NULL, '1');
INSERT INTO `t_dept` VALUES (1, 0, '11');
INSERT INTO `t_dept` VALUES (2, 0, '12');
INSERT INTO `t_dept` VALUES (3, 1, '111');
INSERT INTO `t_dept` VALUES (4, 1, '112');
INSERT INTO `t_dept` VALUES (5, 2, '121');
INSERT INTO `t_dept` VALUES (6, 4, '1121');
INSERT INTO `t_dept` VALUES (7, 4, '1122');
INSERT INTO `t_dept` VALUES (8, 4, '1123');
INSERT INTO `t_dept` VALUES (9, 5, '1211');
![图片[2]-【MySQL】递归查询上下级-小樱博客](https://oss.kvmao.com/2022/06/20220630055955940.png?imageView2/0/format/webp/q/75)
查询上下级sql语句
递归,查询本级以及所有下级
# 递归,查询本级以及所有下级
WITH RECURSIVE temp as (
SELECT t.* FROM t_dept t WHERE id = 0
UNION ALL
SELECT t.* FROM t_dept t INNER JOIN temp ON t.pid = temp.id
)
SELECT * FROM temp;
![图片[3]-【MySQL】递归查询上下级-小樱博客](https://oss.kvmao.com/2022/06/20220630060132241.png?imageView2/0/format/webp/q/75)
递归,查询所有下级
# 递归,查询所有下级
WITH RECURSIVE temp as (
SELECT t.* FROM t_dept t WHERE id = 0
UNION ALL
SELECT t.* FROM t_dept t INNER JOIN temp ON t.pid = temp.id
)
SELECT * FROM temp WHERE id != 0;
![图片[4]-【MySQL】递归查询上下级-小樱博客](https://oss.kvmao.com/2022/06/20220630060230697.png?imageView2/0/format/webp/q/75)
递归,查询本级及所有直属上级
# 递归,查询本级及所有直属上级
WITH RECURSIVE temp as (
SELECT t.* FROM t_dept t WHERE id = 8
UNION ALL
SELECT t.* FROM t_dept t INNER JOIN temp ON t.id = temp.pid
)
SELECT * FROM temp;
![图片[5]-【MySQL】递归查询上下级-小樱博客](https://oss.kvmao.com/2022/06/20220630060340340.png?imageView2/0/format/webp/q/75)
递归,查询所有直属上级
# 递归,查询所有直属上级
WITH RECURSIVE temp as (
SELECT t.* FROM t_dept t WHERE id = 8
UNION ALL
SELECT t.* FROM t_dept t INNER JOIN temp ON t.id = temp.pid
)
SELECT * FROM temp WHERE id != 8;
![图片[6]-【MySQL】递归查询上下级-小樱博客](https://oss.kvmao.com/2022/06/20220630060435990.png?imageView2/0/format/webp/q/75)
© 版权声明
温馨提示:
1.本站大部分内容均收集于网络!若内容若侵犯到您的权益,请联系站长处理!
2.如果您喜欢我们,可开通终身会员,享受全站资源免费下载!
3.本站所有内容只做学习和交流使用。 版权归原作者所有。
THE END
请登录后查看评论内容