关于递归树转换关系树的问题
结构
id pid
A P
B P
A1 A
A2 A
A3 A
B1 B
B2 B
B11 B1
B12 B1
B111 B11
请问怎么得到
P A
P A A1
P A A2
P A A3
P B
P B B1
P B B1 B11
P B B1 B12
P B B1 B11 B111
P B B2
[解决办法]
在这种情况下列的数量是不定的，没法确定到底需要在select 后面写上多少伪列才符合要求，而且即使实现，前台程序语言取起结果来也会非常难处理。
唯一能够实现类似于这种要求的是SYS_CONNECT_BY_PATH
SQL> WITH t as (
2 select 'A' id, 'P' pid from dual union all
3 select 'B' id, 'P' pid from dual union all
4 select 'A1' id, 'A' pid from dual union all
5 select 'A2' id, 'A' pid from dual union all
6 select 'A3' id, 'A' pid from dual union all
7 select 'B1' id, 'B' pid from dual union all
8 select 'B2' id, 'B' pid from dual union all
9 select 'B11' id, 'B1' pid from dual union all
10 select 'B12' id, 'B1' pid from dual union all
11 select 'B111' id, 'B11' pid from dual )
12 select 'P'
[解决办法]
SYS_CONNECT_BY_PATH(ID,'/') from t start with pid = 'P' connect by PRIOR id = pid;
'P'
[解决办法]
SYS_CONNECT_BY_PATH(ID,'/
--------------------------------------------------------------------------------
P/A
P/A/A1
P/A/A2
P/A/A3
P/B
P/B/B1
P/B/B1/B11
P/B/B1/B11/B111
P/B/B1/B12
P/B/B2
10 rows selected
SQL>
