表结构:ID FID NAME
100 层1
101 100 层1-1
102 100 层1-2
200 层2
201 200 层2-1问题是这样的,我的查询语句
select * from et_fl_proc_type connect by prior type_code=parent_type_code
查询出的结果为:
ID FID NAME
101 100 层1-1
102 100 层1-2
201 200 层2-1
100 层1
101 100 层1-1
102 100 层1-2
200 层2
201 200 层2-1
为什么先列出的是所有的子节点?然后才是树结构?
要怎样解决?
大家帮帮忘O(∩_∩)O谢谢!
100 层1
101 100 层1-1
102 100 层1-2
200 层2
201 200 层2-1问题是这样的,我的查询语句
select * from et_fl_proc_type connect by prior type_code=parent_type_code
查询出的结果为:
ID FID NAME
101 100 层1-1
102 100 层1-2
201 200 层2-1
100 层1
101 100 层1-1
102 100 层1-2
200 层2
201 200 层2-1
为什么先列出的是所有的子节点?然后才是树结构?
要怎样解决?
大家帮帮忘O(∩_∩)O谢谢!
connect by prior type_code=parent_type_code
start with fid is null
WITH t AS(
SELECT 100 id,NULL fid,'1' NAME FROM dual
UNION ALL
SELECT 101,100,'1-1' FROM dual
UNION ALL
SELECT 102,100,'1-2' FROM dual
UNION ALL
SELECT 1011,101,'101-1' FROM dual
UNION ALL
SELECT 200,NULL,'2' FROM dual
UNION ALL
SELECT 201,200,'2-1' FROM dual
)
SELECT * FROM t CONNECT BY PRIOR id=fid;
ID, FID, NAME
101 100 1-1
1011 101 101-1
102 100 1-2
1011 101 101-1
201 200 2-1
100 1
101 100 1-1
1011 101 101-1
102 100 1-2
200 2
201 200 2-1