我总算看明白了，楼主是要按深度优先输出树，然后用笔在树的外围画轮廓，求画的顺序。
下面的写法有个隐含前提就是CONNECT BY按照深度优先输出（直接取ROWNUM），然后通过比较前后节点的LEVEL来决定是否要回溯。
WITH DATA AS (
SELECT 1 AS type_id, '商品' AS name ,0AS parent FROM DUAL
UNION ALL SELECT 2 AS type_id, '食品',1AS parent FROM DUAL
UNION ALL SELECT 3 AS type_id, '肉类',2AS parent FROM DUAL
UNION ALL SELECT 4 AS type_id, '猪肉',3AS parent FROM DUAL
UNION ALL SELECT 5 AS type_id, '蔬菜类',2AS parent FROM DUAL
UNION ALL SELECT 6 AS type_id, '白菜',5AS parent FROM DUAL
UNION ALL SELECT 7 AS type_id, '电器',1AS parent FROM DUAL
UNION ALL SELECT 8 AS type_id, '电视机',7AS parent FROM DUAL
UNION ALL SELECT 9 AS type_id, '电冰箱',7AS parent FROM DUAL
)
,d AS (
SELECT data.*,LEVEL lvl,ROWNUM seq,CONNECT_BY_ROOT(type_id) root_id
FROM DATA
START WITH parent = 0
CONNECT BY parent = PRIOR type_id
ORDER SIBLINGS BY type_id-------- 同一父亲的子节点按type_id顺序
)
,t AS (
SELECT d2.*
,LAG(lvl,1,0) OVER(PARTITION BY root_id ORDER BY seq) last_lvl
,LAG(type_id) OVER(PARTITION BY root_id ORDER BY seq) last_id
FROM (SELECT d.* FROM d
UNION ALL ---- 拼上一个伪节点，使得最后的右侧轮廓能够画完。lvl=0肯定小于上一点的深度，所以会触发回溯
SELECT NULL AS type_id,NULL AS name,0 AS parent,0 AS lvl,seq+1 AS seq,root_id
FROM d
WHERE seq=(SELECT MAX(seq) FROM d)
) d2
)
,t2 AS (
SELECT t.*
,CASE WHEN lvlt.parent
)
END||','||type_id AS str
FROM t
)
,t4 AS
(SELECT back_id,MAX(lft) lft,MAX(rgt) rgt
FROM (SELECT t3.*
,CASE WHEN type_id=back_id THEN ROWNUM END AS lft
,CASE WHEN type_id=back_id THEN NULL ELSE ROWNUM END AS rgt
FROM (SELECT t2.*
,REGEXP_SUBSTR(str,'[^,]+',1,cnt) back_id
FROM t2
,(SELECT LEVEL cnt FROM DUAL CONNECT BY LEVEL<=(SELECT MAX(lvl) FROM t))
WHERE REGEXP_SUBSTR(str,'[^,]+',1,cnt) IS NOT NULL
ORDER BY seq,cnt
) t3
)
GROUP BY back_id
)
SELECT data.type_id,data.name,t4.lft,t4.rgt
FROM t4 JOIN data ON t4.back_id=data.type_id;
|
