MySQL递归查询

1、先创建测试环境

1、创建部门表

DROP TABLE IF EXISTS `dept`;
CREATE TABLE `dept`  (
  `id` varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
  `name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
  `pid` varchar(10) 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;

2、往部门表中插入测试数据

INSERT INTO `dept`(`id`, `name`, `pid`) VALUES ('1000', '总公司', NULL);
INSERT INTO `dept`(`id`, `name`, `pid`) VALUES ('1001', '北京分公司', '1000');
INSERT INTO `dept`(`id`, `name`, `pid`) VALUES ('1002', '上海分公司', '1000');
INSERT INTO `dept`(`id`, `name`, `pid`) VALUES ('1003', '北京研发部', '1001');
INSERT INTO `dept`(`id`, `name`, `pid`) VALUES ('1004', '北京财务部', '1001');
INSERT INTO `dept`(`id`, `name`, `pid`) VALUES ('1005', '北京市场部', '1001');
INSERT INTO `dept`(`id`, `name`, `pid`) VALUES ('1006', '北京研发一部', '1003');
INSERT INTO `dept`(`id`, `name`, `pid`) VALUES ('1007', '北京研发二部', '1003');
INSERT INTO `dept`(`id`, `name`, `pid`) VALUES ('1008', '北京研发一部一小组', '1006');
INSERT INTO `dept`(`id`, `name`, `pid`) VALUES ('1009', '北京研发一部二小组', '1006');
INSERT INTO `dept`(`id`, `name`, `pid`) VALUES ('1010', '北京研发二部一小组', '1007');
INSERT INTO `dept`(`id`, `name`, `pid`) VALUES ('1011', '北京研发二部二小组', '1007');
INSERT INTO `dept`(`id`, `name`, `pid`) VALUES ('1012', '北京市场一部', '1005');
INSERT INTO `dept`(`id`, `name`, `pid`) VALUES ('1013', '上海研发部', '1002');
INSERT INTO `dept`(`id`, `name`, `pid`) VALUES ('1014', '上海研发一部', '1013');
INSERT INTO `dept`(`id`, `name`, `pid`) VALUES ('1015', '上海研发二部', '1013');
commit;

2、向上递归查询

-- 根据一个子节点id，查询所有父节点（包含⾃⾝）
SELECT t2.id, t2.name, t2.pid
 FROM (SELECT @r as _id,
              (SELECT @r := pid FROM dept WHERE id = _id) as pid,
              @l := @l + 1 as lvl
         FROM (SELECT @r := '1014', @l := 0) vars, dept as h
        WHERE @r <> 0) t1
 JOIN dept t2
   ON t1._id = t2.id
ORDER BY T1.lvl DESC; 

其中 @r := 1014 表示查询 id 为 1014 的所有父类

id  |name  |pid |
----+------+----+
1000|总公司   |    |
1002|上海分公司 |1000|
1013|上海研发部 |1002|
1014|上海研发一部|1013|

3、向下递归查询

-- 根据⼀个⽗节点查询所有⼦节点（包含⾃⾝）
SELECT au.id, au.name, au.pid
  FROM (SELECT * FROM dept WHERE pid IS NOT NULL) au,
       (SELECT @pid := '1002') pd
 WHERE FIND_IN_SET(pid, @pid) > 0
   AND @pid := concat(@pid, ',', id)
UNION
SELECT id, name, pid
  FROM dept
 WHERE id = '1002'
 ORDER BY id;  

其中 @r := 1002 表示查询 id 为 1002 的所有子类

id  |name  |pid |
----+------+----+
1002|上海分公司 |1000|
1013|上海研发部 |1002|
1014|上海研发一部|1013|
1015|上海研发二部|1013|

4、向下递归查询

-- 根据多个⽗节点查询所有⼦节点（包含⾃⾝）
-- 根据多个⽗节点查询所有⼦节点（包含⾃⾝）
SELECT au.id, au.name, au.pid
  FROM (SELECT * FROM dept WHERE pid IS NOT NULL) au,
       (SELECT @pid := '1002,1005') pd
 WHERE FIND_IN_SET(pid, @pid) > 0 and @pid := concat(@pid, ',', id)
UNION
SELECT id, name, pid
  FROM dept
 WHERE FIND_IN_SET(id, @pid) > 0
 ORDER BY id;   

其中 @r := 1002 表示查询 id 为 1002 的所有子类

id  |name  |pid |
----+------+----+
1002|上海分公司 |1000|
1005|北京市场部 |1001|
1012|北京市场一部|1005|
1013|上海研发部 |1002|
1014|上海研发一部|1013|
1015|上海研发二部|1013|

参考：https://www.cnblogs.com/guohu/p/14990788.html