id CODE PARENT_ID
1 A 0
2 B 1
3 C 2
4 D 2
5 E 1
6 F 5
7 G 6需要查出来:
level1 level2 level3 level4
A B C
A B D
A E F G也就是一个父子表,我要按它最大层级转换成列表除了一层层查后拼出来,还有更好的方法吗?请各位指点
1 A 0
2 B 1
3 C 2
4 D 2
5 E 1
6 F 5
7 G 6需要查出来:
level1 level2 level3 level4
A B C
A B D
A E F G也就是一个父子表,我要按它最大层级转换成列表除了一层层查后拼出来,还有更好的方法吗?请各位指点
with a as(
select '1' as id, 'A' as code, '0' as parent_id from dual
union
select '2', 'B', '1' from dual
union
select '3', 'C', '2' from dual
union
select '4', 'D', '2' from dual
union
select '5', 'E', '1' from dual
union
select '6', 'F', '5' from dual
union
select '7', 'G', '6' from dual
)
select sys_connect_by_path(code, '>') "Path" from a
START WITH parent_id = '0'
connect by prior id = parent_id;
with a as(
select '1' as id, 'A' as code, '0' as parent_id from dual
union
select '2', 'B', '1' from dual
union
select '3', 'C', '2' from dual
union
select '4', 'D', '2' from dual
union
select '5', 'E', '1' from dual
union
select '6', 'F', '5' from dual
union
select '7', 'G', '6' from dual
)
select sys_connect_by_path(code, '>') "Path" , level from a
where level >=3
start with parent_id = '0'
connect by prior id = parent_id;
with a as(
select '1' as id, 'A' as code, '0' as parent_id from dual
union
select '2', 'B', '1' from dual
union
select '3', 'C', '2' from dual
union
select '4', 'D', '2' from dual
union
select '5', 'E', '1' from dual
union
select '6', 'F', '5' from dual
union
select '7', 'G', '6' from dual
)select path from
(
select path,rn,nvl(lead(rn,1)over(order by 1),0) rm
from
(
select substr(sys_connect_by_path(code, '-'),2) Path ,level rn from a
--where level >=3
start with parent_id = '0'
connect by prior id = parent_id
)
)
where rn>=rm --result:1 A-B-C
2 A-B-D
3 A-E-F-G
select '1' as id, 'A' as code, '0' as parent_id from dual
union
select '2', 'B', '1' from dual
union
select '3', 'C', '2' from dual
union
select '4', 'D', '2' from dual
union
select '5', 'E', '1' from dual
union
select '6', 'F', '5' from dual
union
select '7', 'G', '6' from dual
)
, b as (
select a.*, sys_connect_by_path(code, '>') "Path", level from a
START WITH parent_id = '0'
connect by prior id = parent_id
)
select substr("Path", 2, 1) L1, substr("Path", 4, 1) L2, substr("Path", 6, 1) L3, substr("Path", 8, 1) L4
from b
where not exists(select * from a where a.parent_id=b.id); --10g 可以这样: where CONNECT_BY_ISLEAF=1--result:
L1 L2 L3 L4
A B C
A B D
A E F G
非常谢谢你正确的解答。to:cosio,yixilan
同样谢谢你们的回答,为我其它报表的样式提供了思路