--这个是查询某节点所有上级的例子
--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
--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
解决方案 »
- plsql中to_char转换时间
- 数组排序报找不到数据的错误,还有如何将数组中的数值,重新赋值给原数据单元
- 新手关于索引的问题。为何在以下情况下,建B*-树索引占用100K空间,而位图索引只占16K。我是新手,请大家进来看看吧。
- 求一个查询语句!!!!!!
- 急问rownum怎样实现指定字段重新排序?
- 我目前想把网页中的数据导入到oracle数据库,我有数据html文件25mb,各位有什么方法
- 数据比较问题,急!
- 如何指定一个过程在个一个时间执行一次????
- 有人在win2003上装oracle成功吗?
- Powerdesign的pdm文件怎么导入到oracle里??
- 新手,求写一个Linux自动登陆ORACLE数据库的.sh文件
- 求问OBIEE主页登陆不上的问题
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