MySQL 递归查询
valten Lv4

创建测试环境

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

1. 创建表

1
2
3
4
5
6
7
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. 插入测试数据

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
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. 查看一下刚才插入的数据

1
select * from dept;

结果如下:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
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,查询所有父节点(包含⾃⾝)

1
2
3
4
5
6
7
8
9
10
-- 根据一个子节点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 的所有父类

|name |pid |
1
2
3
4
5
6
id  |name  |pid |
----+------+----+
1000|总公司 | |
1002|上海分公司 |1000|
1013|上海研发部 |1002|
1014|上海研发一部|1013|

向下递归

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

1
2
3
4
5
6
7
8
9
10
11
-- 根据⼀个⽗节点查询所有⼦节点(包含⾃⾝)
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;
1
2
3
4
5
6
id  |name  |pid |
----+------+----+
1002|上海分公司 |1000|
1013|上海研发部 |1002|
1014|上海研发一部|1013|
1015|上海研发二部|1013|

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

1
2
3
4
5
6
7
8
9
10
-- 根据多个⽗节点查询所有⼦节点(包含⾃⾝)
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;
1
2
3
4
5
6
7
8
id  |name  |pid |
----+------+----+
1002|上海分公司 |1000|
1005|北京市场部 |1001|
1012|北京市场一部|1005|
1013|上海研发部 |1002|
1014|上海研发一部|1013|
1015|上海研发二部|1013|
  • 本文标题:MySQL 递归查询
  • 本文作者:valten
  • 创建时间:2023-07-22 21:13:25
  • 本文链接:https://valtenhyl.github.io/MySQL/mysql5.7-recursive/
  • 版权声明:本博客所有文章除特别声明外,均采用 BY-NC-SA 许可协议。转载请注明出处!
 评论