--测试数据
create table test_lvl1 (id number, parent_id number, name varchar2(10));
insert into test_lvl1 values (1,null,'SLI1');
insert into test_lvl1 values (2,1,'SLI2');
insert into test_lvl1 values (3,1,'SLI3');
insert into test_lvl1 values (4,null,'SLI4');
insert into test_lvl1 values (5,2,'SLI5');
insert into test_lvl1 values (6,3,'SLI6');
insert into test_lvl1 values (7,5,'SLI7');
select * from test_lvl1;
/*
解释一下:
1. Start with表示从那一层开始的,后面跟表达式,如: pid=0,
寻找继承关系时,指定的顶点,如果需要对整个表进行整理,比较常用
的作法是: PID is null. 例如这种情况顶点的Parent_ID显然是NULL,
所以从PID is null开始无疑是最完整的。
2. prior 表示返回所以符合这种条件(如id=pid)的connect by操作结果.
*/
select name, ID, PARENT_ID, LEVEL
from test_lvl1
start with parent_id is null
connect by prior id = PARENT_ID --连接条件
order by id
-- 使用连接查询
select a.* ,b.name as parentName
from test_lvl1 a
left join (select name, ID, PARENT_ID, LEVEL
from test_lvl1
start with parent_id is null
connect by prior id = PARENT_ID) b
on a.parent_id = b.id
order by a.id
-- sql 1992语法连接查询
select a.*, b.name as parent_name
from (select name, ID, PARENT_ID, LEVEL
from test_lvl1
start with parent_id is null
connect by prior id = PARENT_ID) a,
test_lvl1 b
where a.parent_id = b.id(+) -- + 表示补充,b表就是匹配表,左表全部显示。
order by a.id