最近弄到层次话查询,树形表如下
id name fatherid depth
1 aaa 0
2 aaa-1 1 1
3 aaa-a 2 2
4 bbb 0
5 bbb-1 4 1
6 ccc 0
要求用以上表输出2个结果
第一种是
bottom_id all_name top_id
3 aaa>>aaa-1>>aaa-a 1第二种是
bottom_id all_name top_id
3 aaa>>aaa-1>>aaa-a 1
5 bbb>>bbb-1 4
6 ccc
希望各位大虾能解出以上问题来,最好使用动态sql,禁用临时表
id name fatherid depth
1 aaa 0
2 aaa-1 1 1
3 aaa-a 2 2
4 bbb 0
5 bbb-1 4 1
6 ccc 0
要求用以上表输出2个结果
第一种是
bottom_id all_name top_id
3 aaa>>aaa-1>>aaa-a 1第二种是
bottom_id all_name top_id
3 aaa>>aaa-1>>aaa-a 1
5 bbb>>bbb-1 4
6 ccc
希望各位大虾能解出以上问题来,最好使用动态sql,禁用临时表
不如这样:第一种:
SELECT MAX(ID) AS BOTTOM_ID,
LTRIM(MAX(SYS_CONNECT_BY_PATH(NAME, '>>')), '>>') AS ALL_NAME,
MIN(ID) AS TOP_ID
FROM TEST_FA
START WITH ID = 1
CONNECT BY FATHERID = PRIOR ID
ORDER BY NAME
/
FROM (
SELECT BOTTOM_ID, TOP_ID, B.ALL_NAME,
ROW_NUMBER() OVER (PARTITION BY TOP_ID ORDER BY DEPTH DESC) AS SEQ
FROM (
SELECT A.ID AS BOTTOM_ID, A.DEPTH,
LTRIM(SYS_CONNECT_BY_PATH(NAME, '>>'), '>>') AS ALL_NAME,
(SELECT MIN(ID) FROM TEST_FA START WITH ID = A.ID CONNECT BY PRIOR FATHERID = ID) AS TOP_ID
FROM TEST_FA A
START WITH DEPTH = 0
CONNECT BY FATHERID = PRIOR ID
) B
) C
WHERE C.SEQ = 1
/