oracle获取子集数据（类似于树形菜单）

 

CREATE TABLE T_TREE (ID NUMBER PRIMARY KEY, FATHER_ID NUMBER, NAME VARCHAR2(30)); 

 

INSERT INTO T_TREE VALUES (1, 0, 'A'); 

INSERT INTO T_TREE VALUES (2, 1, 'BC');  

INSERT INTO T_TREE VALUES (3, 1, 'DE');  

INSERT INTO T_TREE VALUES (4, 1, 'FG');  

INSERT INTO T_TREE VALUES (5, 2, 'HIJ');  

INSERT INTO T_TREE VALUES (6, 4, 'KLM');  

INSERT INTO T_TREE VALUES (7, 6, 'NOPQ');  

 

SELECT ID, FATHER_ID, NAME, CONNECT_BY_ROOT(NAME) ROOT_NAME  

FROM T_TREE  

START WITH FATHER_ID = 1  

CONNECT BY PRIOR ID = FATHER_ID;