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.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|