Oracle与Mysql的递归查询实现

1. 表结构

id name pid
1 四川 0
2 广东 0
3 成都 1
4 郫县 3
5 深圳 2

1.1 建表SQL语句（Mysql）

1
2
3
4
5
6
CREATE TABLE `test_treeselect`  (
  `id` int(0) NOT NULL,
  `name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `pid` int(0) NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

1.2 插入数据

1
2
3
4
5
INSERT INTO `test_treeselect` VALUES (1, '四川', 0);
INSERT INTO `test_treeselect` VALUES (2, '广东', 0);
INSERT INTO `test_treeselect` VALUES (3, '成都', 1);
INSERT INTO `test_treeselect` VALUES (4, '郫县', 3);
INSERT INTO `test_treeselect` VALUES (5, '深圳', 2);

2. SQL实现

​ Oracle 实现递归查询使用的是 start with ... connect by prior ...

​ MySql 使用 with recursive temp as ( ... ) select * from temp

2.1 查询自己及其子节点（查询四川和它的下级）

Oracle

1
select * from test_treeselect start with id = 1 connect by prior id = pid

Mysql

1
2
3
4
with recursive temp as (
	select * from test_treeselect r where r.id = 1
	union all select r.* from test_treeselect r,temp t where t.id = r.pid
)select * from temp

结果

id name pid
1 四川 0
3 成都 1
4 郫县 3

2.2 查询子节点（查询四川的下级）

Oracle

1
select * from test_treeselect start with pid = 1 connect by prior id = pid

Mysql

1
2
3
4
with recursive temp as (
	select * from test_treeselect r where r.pid = 1
	union all select r.* from test_treeselect r,temp t where t.id = r.pid
)select * from temp

结果

id name pid
3 成都 1
4 郫县 3

2.3 查询自己及其父节点（查询成都和它的上级）

Oracle

1
select * from test_treeselect start with id = 3 connect by prior pid = id

Mysql

1
2
3
4
with recursive temp as (
	select * from test_treeselect r where r.id = 3
	union all select r.* from test_treeselect r,temp t where t.pid = r.id
)select * from temp

结果

id name pid
3 成都 1
1 四川 0

2.4 查询父节点（查询郫县的上级）

​ 不知道为什么， Oracle 和 Mysql 好像都不能只查自己的父节点，只能通过找到 pid 然后用 2.3 的方法查询父节点，如果你有什么好的方法可以留言给我哦！

Oracle

1
select * from test_treeselect start with id = (select pid from test_treeselect where id = 4) connect by  prior pid =  id

Mysql

1
2
3
4
with recursive temp as (
	select * from test_treeselect r where r.id = (select pid from test_treeselect where id = 4)
	union all select r.* from test_treeselect r,temp t where t.pid = r.id
)select * from temp

结果

id name pid
3 成都 1
1 四川 0