MySQL5.7 实现递归查询

创建测试环境

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;

3. 查看一下刚才插入的数据

select * from dept;

结果如下：

id  |name     |pid |
----+---------+----+
1000|总公司      |    |
1001|北京分公司    |1000|
1002|上海分公司    |1000|
1003|北京研发部    |1001|
1004|北京财务部    |1001|
1005|北京市场部    |1001|
1006|北京研发一部   |1003|
1007|北京研发二部   |1003|
1008|北京研发一部一小组|1006|
1009|北京研发一部二小组|1006|
1010|北京研发二部一小组|1007|
1011|北京研发二部二小组|1007|
1012|北京市场一部   |1005|
1013|上海研发部    |1002|
1014|上海研发一部   |1013|
1015|上海研发二部   |1013|

向上递归

根据一个子节点id，查询所有父节点（包含⾃⾝）

-- 根据一个子节点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|

向下递归

根据⼀个⽗节点查询所有⼦节点（包含⾃⾝）

-- 根据⼀个⽗节点查询所有⼦节点（包含⾃⾝）
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;

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

根据多个⽗节点查询所有⼦节点（包含⾃⾝）

-- 根据多个⽗节点查询所有⼦节点（包含⾃⾝）
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;

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

参考
https://www.cnblogs.com/guohu/p/14990788.html
https://wenku.baidu.com/view/6bb57f0e925f804d2b160b4e767f5acfa1c783cf.html?fr=income1-wk_app_search_ctr-search