记录笔记

【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】递归查询上下级-小樱博客
© 版权声明
THE END
喜欢本站内容,请点【分享】【赞】和【收藏】
点赞6赞赏 分享
评论 共1条
头像
友好交流,请勿发纯表情,请勿灌水
提交
头像

昵称

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

    请登录后查看评论内容