单表级联查询sql

jackey_zhang 2012-12-26 10:18:05

数据库字段
BIANHAO MINGCHENG SHANGJIBIANHAO
1 AAA
2 BBB 1
3 CCC 2
4 DDD 1
5 EEE 4
... ... ...

每条记录都有对应的上级编号，最上级无编号，要求随便给一个编号，就可以查询出该编号的上级和下级所有
等级有多级，不固定，有没有高手能解决啊。。。求大神帮助！！

...全文

374 6 打赏收藏转发到动态举报

写回复

用AI写文章

6 条回复

切换为时间正序

请发表友善的回复…

发表回复

kandari 2013-12-05

打赏
举报

不错，我也正需要这个效果谢谢各位倾情分享

善若止水 2012-12-28

打赏
举报

学习了，系统连接函数

izj 2012-12-27

打赏
举报

不知道你要的是不是这个，先贴出来


with test
as 
(
select 1 as id , 0 as gid from dual 
union all 
select 2 as id , 1 as gid from dual 
union all 
select 3 as id , 0 as gid from dual 
union all 
select 4 as id , 0 as gid from dual 
union all 
select 5 as id , 2 as gid from dual 
union all 
select 6 as id , 2 as gid from dual 
union all 
select 7 as id , 3 as gid from dual 
)
select * from 
(
 select sys_connect_by_path(id,'>') as level from test
 start with id is not null connect by prior id = gid 
)
where level like '%'||&value||'%'


value赋值 5 

出来结果
---------------
>1>2>5
>2>5
>5

shadow1986a 2012-12-26

打赏
举报

引用 2 楼 hupeng213 的回复:

--这个是查询某节点所有上级的例子 --sys_connect_by_path(folder,'-->') AS allPath WITH tbFolder AS ( SELECT 1 AS orgcode, '根目录' AS folder, 0 AS parentorgcode FROM dual UNION ALL SELECT 2, '第一级A', 1 FROM dua……

这个应该可以实现。

hupeng213 2012-12-26

打赏
举报

--这个是查询某节点所有上级的例子 --sys_connect_by_path(folder,'-->') AS allPath WITH tbFolder AS ( SELECT 1 AS orgcode, '根目录' AS folder, 0 AS parentorgcode FROM dual UNION ALL SELECT 2, '第一级A', 1 FROM dual UNION ALL SELECT 3, '第一级B', 1 FROM dual UNION ALL SELECT 4, '第二级A', 2 FROM dual UNION ALL SELECT 5, '第二级B', 2 FROM dual UNION ALL SELECT 6, '第三级A', 4 FROM dual UNION ALL SELECT 7, '第三级B', 5 FROM dual ) SELECT t.orgcode, t.folder, t.parentorgcode, sys_connect_by_path(folder,'-->') AS allPath FROM tbFolder t START WITH orgcode = 1 CONNECT BY prior orgcode = parentorgcode

izj 2012-12-26