级联查询有很多教程示例,但是没有找到求特定子孙到根的路径的例子,折腾了一番总算想出方法了。
现假设我们拥有一个菜单表t_menu,其中只有三个字段:id、name和parent_id。它们是具有父子关系的,最顶级的菜单对应的parent_id为0。现假设我们拥有如下记录:
id | name | parent_id |
1 | 菜单01 | 0 |
2 | 菜单02 | 0 |
3 | 菜单03 | 0 |
4 | 菜单0101 | 1 |
5 | 菜单0102 | 1 |
6 | 菜单0103 | 1 |
7 | 菜单010101 | 4 |
8 | 菜单010201 | 5 |
9 | 菜单010301 | 6 |
10 | 菜单0201 | 2 |
11 | 菜单0202 | 2 |
12 | 菜单020101 | 10 |
13 | 菜单020102 | 10 |
14 | 菜单020103 | 10 |
15 | 菜单0301 | 3 |
16 | 菜单0302 | 3 |
17 | 菜单030201 | 16 |
18 | 菜单030202 | 16 |
19 | 菜单030203 | 16 |
(示例数据来自一篇不错的级联查询教程)
假设需要求菜单parentId为16的数据到菜单03的路径,用
select *,sys_connect_by_path(name,'/')
from t_menu where parent_id = 16
start with id is not null
connect by prior id = parent_id
得出的是每个节点往下的路径。
把prior 放在parent_id 前面,可以得出全路径,但是不好提取到根的路径。而且如果数据库很大, start with 用 is not null 会很慢。
这里我想到一个点子
select * from t_menu t left join ( select connect_by_root id as id ,sys_connect_by_path(name,'/') path from t_menu where id=3 start with (select id from t_menu where parent_id =16) connect by id =prior parent_id) t1 on t.id=t1.id
用connect_by_root 来取出path的actul id,再和原表左联。如有更好的方法,欢迎交流