表A 中,有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获取后记录为:
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 为0 代表无。 例:表A:
id name fatherID
1 aaaaa 0
2 bbbbb 1
3 ccccc 2
4 ddddd 0
5 eeeee 3
6 fffff 5获取后记录为:
id name fatherID
1 aaaaa 0
2 bbbbb 1
3 ccccc 2
5 eeeee 3
6 fffff 5
with tbl as
(
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 * from tbl
connect by prior id = fatherid
start with fatherid = 0;
ID NAME FATHERID
---------- ----- ----------
1 aaaaa 0
2 bbbbb 1
3 ccccc 2
5 eeeee 3
6 fffff 5
4 ddddd 0
谢啦,晚上回去试下, 那个 如果只要查询某ID的所有父记录(父记录还有父记录) 是不是把加个 and id=6就可以了吖?
那要怎么获取某ID的 所有父ID啊?
(
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
--这样子可以处理ID=6 的父ID
6 0,1,2,3,5
with tbl as
(
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
)
-- 查找所有父ID
select * from tbl
start with id = 6
connect by id = prior fatherid;