树形构造查询

树形结构查询
引用

--从根节点向叶子节点查询
SELECT A.*, LEVEL
  FROM TABLE_A A
START WITH PARENT_ID = 100
CONNECT BY PRIOR ID = PARENT_ID
ORDER SIBLINGS BY ID;

--从叶子节点向根节点查询
SELECT A.*, LEVEL
  FROM  TABLE_A A
START WITH ID = 10010011
CONNECT BY PRIOR PARENT_ID = ID
ORDER SIBLINGS BY ID;



SELECT MENU_ID,
       PARENT_MENU_ID,
       LEVEL,
       LPAD(' ', 6 * (LEVEL - 1)) || MENU_TITLE,
       LTRIM(SYS_CONNECT_BY_PATH(MENU_TITLE, '->'), '->')
  FROM MENU
START WITH MENU_ID = 1607
CONNECT BY PRIOR MENU_ID = PARENT_MENU_ID
ORDER SIBLINGS BY MENU_ID;