表A 中,有ID,父ID,名称,三个字段。
如何通过id获取某ID的父ID记录(父ID还有父ID)并按ID排序。 父ID 其实就是指向ID。 如何获取所有? 父ID 为0 代表无。 例:表A:
id name fatherID
1 aaaaa 0
2 bbbbb 1
3 ccccc 2
4 ddddd 0
5 eeeee 3
6 fffff 5传入 where id=6 获取后记录为:
id name fatherID
1 aaaaa 0
2 bbbbb 1
3 ccccc 2
5 eeeee 3
6 fffff 5
如何通过id获取某ID的父ID记录(父ID还有父ID)并按ID排序。 父ID 其实就是指向ID。 如何获取所有? 父ID 为0 代表无。 例:表A:
id name fatherID
1 aaaaa 0
2 bbbbb 1
3 ccccc 2
4 ddddd 0
5 eeeee 3
6 fffff 5传入 where id=6 获取后记录为:
id name fatherID
1 aaaaa 0
2 bbbbb 1
3 ccccc 2
5 eeeee 3
6 fffff 5
解决方案 »
- SQLPLUS中导出长字符串格式的数据怎么处理
- 查询结果自动去除了重复行?
- oracle中对clob字段查询总失败
- 请教:如何在PL/SQL dev中调试带有输入参数为数组的存储过程
- oracle 查询最高分
- Oracle like 是不是只支持%和_两个通配符?
- 简单问题:ORACLE8.05版本怎么不能删除列?
- SQLPLUS的问题?
- 100分诚心求教!无论写过程还是函数总是报错
- 我在ORACLE里使用update...from进行多表更新,为什么报from语法错误“UPDATE A SET A.STATUS = 0 FROM ONE A,TWO B WHERE A.id=B.id”
- 菜鸟求解一个简单问题
- 请教SQL
[SYS@myoracle] SQL>WITH A AS(
2 SELECT 1 ID,'aaaaa' NAME , 0 fatherID FROM DUAL UNION ALL
3 SELECT 2 ID,'bbbbb' NAME , 1 fatherID FROM DUAL UNION ALL
4 SELECT 3 ID,'ccccc' NAME , 2 fatherID FROM DUAL UNION ALL
5 SELECT 4 ID,'ddddd' NAME , 0 fatherID FROM DUAL UNION ALL
6 SELECT 5 ID,'eeeee' NAME , 3 fatherID FROM DUAL UNION ALL
7 SELECT 6 ID,'fffff' NAME , 5 fatherID FROM DUAL
8 )SELECT ID,NAME, fatherID
9 FROM A
10 START WITH ID = 6
11 CONNECT BY PRIOR fatherID = ID
12 ORDER BY ID
13 ; ID NAME FATHERID
---------- ----- ----------
1 aaaaa 0
2 bbbbb 1
3 ccccc 2
5 eeeee 3
6 fffff 5
(
select 1 as id, 'aaaaa' as name, 0 as fatherID from dual
union all
select 2 as id, 'bbbbb' as name, 1 as fatherID from dual
union all
select 3 as id, 'ccccc' as name, 2 as fatherID from dual
union all
select 4 as id, 'ddddd' as name, 0 as fatherID from dual
union all
select 5 as id, 'eeeee' as name, 3 as fatherID from dual
union all
select 6 as id, 'fffff' as name, 5 as fatherID from dual
)
select id,max(substr(sys_connect_by_path(fatherid,','),2))
from tbl
where id='6'
connect by prior id = fatherid
start with fatherid =0
group by id--result:
6 0,1,2,3,5
start with id='1'
connect by prior id = fatherID;