幻灯片-记录笔记

【MySQL】递归查询上下级

环境

需要MySQL8.0+

上下级关系图

图片[1]-【MySQL】递归查询上下级-记录笔记
建表语句
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】递归查询上下级-记录笔记

查询上下级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】递归查询上下级-记录笔记

递归,查询所有下级

# 递归,查询所有下级
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】递归查询上下级-记录笔记

递归,查询本级及所有直属上级

# 递归,查询本级及所有直属上级
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】递归查询上下级-记录笔记

递归,查询所有直属上级

# 递归,查询所有直属上级
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】递归查询上下级-记录笔记
温馨提示:本文最后更新于2022-06-30 14:09:13,某些文章具有时效性,若有错误或已失效,请在下方留言或联系小樱社长
© 版权声明
THE END
喜欢本站内容,请点【分享】【赞】和【收藏】
点赞5 分享
评论 共1条
头像
欢迎您留下宝贵的见解!
提交
头像

昵称

夸夸
夸夸
还有吗!没看够!
取消
昵称表情代码图片