WITH temp AS (
SELECT 1 ID, 0 parent_id, '海南' menu_name FROM dual
UNION ALL
SELECT 2 ID, 0 parent_id, '广东' menu_name FROM dual
UNION ALL
SELECT 3 ID, 2 parent_id, '广州' menu_name FROM dual
UNION ALL
SELECT 4 ID, 1 parent_id, '海口' menu_name FROM dual
UNION ALL
SELECT 5 ID, 1 parent_id, '三亚' menu_name FROM dual
)
SELECT ID, parent_id, menu_name FROM (
SELECT t.*, SYS_CONNECT_BY_PATH(to_char(t.ID, '0000000000'), '-') x
FROM temp t
START WITH t.parent_id = 0
CONNECT BY PRIOR ID = parent_id
)
ORDER BY x, id
[ 本帖最后由 3833020 于 2011-1-12 17:55 编辑 ]
|