表 test 结构: ID , PID ,数据如下:
ID PID
----------------------
C 0
B 0
A 0
A2 A
A2 A
A1 A
A1 A
A12 A1
A11 A1
A22 A2
A21 A2
B11 B
B2 B
B1 B
C1 C想得到如下集合:
0-A-A1-A11
0-A-A1-A12
0-A-A2-A21
0-A-A2-A22
0-B-B1-B11
0-B-B2
0-C-C1
ID PID
----------------------
C 0
B 0
A 0
A2 A
A2 A
A1 A
A1 A
A12 A1
A11 A1
A22 A2
A21 A2
B11 B
B2 B
B1 B
C1 C想得到如下集合:
0-A-A1-A11
0-A-A1-A12
0-A-A2-A21
0-A-A2-A22
0-B-B1-B11
0-B-B2
0-C-C1
解决方案 »
- oracle安装问题
- oracle怎样强制执行某个索引
- imp导入一个dmp文件时出问题!急求~~~~~
- 如何在oracle中选择前10条语句?(像select top 10 from biao)一样?
- 如何设置UTL_FILE_DIR参数。在线给分
- java.sql.SQLException: No more data to read from socket
- oracle 8.17 for nt 错误,如何解决!请高手指点! wait online
- 应用服务器连接数据库的问题
- oracle 故障原因
- Oracle中的having问题
- 求方法、求思路
- exp导出来的数据,跟rman备份出来的有什么不同?
PL/SQL中树结构查询的问题
http://topic.csdn.net/u/20110914/23/8c95df28-b93c-4c33-9454-3095036b65e6.html?82166
with t as (
select 'C' ID, '0' PID from dual union
select 'B', '0' from dual union
select 'A', '0' from dual union
select 'A2', 'A' from dual union
select 'A2', 'A' from dual union
select 'A1', 'A' from dual union
select 'A1', 'A' from dual union
select 'A12', 'A1' from dual union
select 'A11', 'A1' from dual union
select 'A22', 'A2' from dual union
select 'A21', 'A2' from dual union
select 'B11', 'B' from dual union
select 'B2', 'B' from dual union
select 'B1', 'B' from dual union
select 'C1', 'C' from dual
)
select '0'||sys_connect_by_path(id,'-')
from t
where connect_by_isleaf = 1
connect by pid = prior id
start with pid = '0';
-- 正解!scott@TBWORA> with t as (
2 select 'C' ID, '0' PID from dual union
3 select 'B', '0' from dual union
4 select 'A', '0' from dual union
5 select 'A2', 'A' from dual union
6 select 'A2', 'A' from dual union
7 select 'A1', 'A' from dual union
8 select 'A1', 'A' from dual union
9 select 'A12', 'A1' from dual union
10 select 'A11', 'A1' from dual union
11 select 'A22', 'A2' from dual union
12 select 'A21', 'A2' from dual union
13 select 'B11', 'B' from dual union
14 select 'B2', 'B' from dual union
15 select 'B1', 'B' from dual union
16 select 'C1', 'C' from dual
17 )
18 select '0'||sys_connect_by_path(id,'-')
19 from t
20 where connect_by_isleaf = 1
21 connect by pid = prior id
22 start with pid = '0';'0'||SYS_CONNECT_BY_PATH(ID,'-')
---------------------------------------------------------------------------------------
0-A-A1-A11
0-A-A1-A12
0-A-A2-A21
0-A-A2-A22
0-B-B1
0-B-B11
0-B-B2
0-C-C1已选择8行。
最好不要写死,最上层的 0 再改一下吧
借鉴一下SELECT
substr(
SYS_CONNECT_BY_PATH(PID, '-')
|| '-' || ID ,
2,
999
) sys_path
FROM 表
START WITH pid = '0'
CONNECT BY PRIOR ID = pid;