start with … connect by 用法讲解:
构建如下table:
ID NAME PID
1 10 0
2 11 1
3 20 0
4 12 1
5 121 2
code example1:
select TBL_TEST.*, level from TBL_TEST
start with pid= 1 -- 可写到 connect by 后面
connect by prior pid = id
ID NAME PID LEVEL
2 11 1 1
1 10 0 2
4 12 1 1
1 10 0 2
code example2:
select TBL_TEST.*, level from TBL_TEST
start with id = 5 -- 可写到 connect by 后面
connect by prior pid = id
ID NAME PID LEVEL
5 121 2 1
2 11 1 2
1 10 0 3
说明:
1、先从start with pid=1 句开始查询 得到 2 11 1 1 =====> level置1;
2、根据pid = id,查询 id=1 句,得到 1 10 0 2 =====> level置2;
3、根据pid = id,查询 id=0 句,未查询到后结束该树枝;
注:prior pid = id 句说明 pid是id的父节点,通过pid查询id