MySQL5.7 实现递归查询

创建测试环境

在线数据库 http://sqlfiddle.com/

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.idORDER 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)UNIONSELECT 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)UNioNSELECT 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

来源地址：https://blog.csdn.net/u014786530/article/details/127300809