select * from unit start with unit_id='查询条件' CONNECT BY PRIOR unit_id=parent_id
CREATE TABLE unit ( unit_id NUMBER, unit_name VARCHAR2(100), parent_id NUMBER );INSERT INTO UNIT SELECT '1','LEVEL1','' FROM DUAL UNION SELECT '12','LEVEL12','1' FROM DUAL UNION SELECT '13','LEVEL13','1' FROM DUAL UNION SELECT '121','LEVEL121','12' FROM DUAL UNION SELECT '131','LEVEL131','13' FROM DUAL UNION SELECT '122','LEVEL122','121' FROM DUAL UNION SELECT '132','LEVEL132','131' FROM DUAL; SELECT LEVEL, T.UNIT_ID ,T.UNIT_NAME FROM UNIT T START WITH T.UNIT_ID = 12 CONNECT BY PRIOR T.UNIT_ID = T.PARENT_ID UNION SELECT LEVEL, T.UNIT_ID ,T.UNIT_NAME FROM UNIT T START WITH T.UNIT_ID = 12 CONNECT BY PRIOR T.PARENT_ID = T.UNIT_ID
上面写错了,应该是 select * from unit start with unit_id='查询条件' CONNECT BY PRIOR parent_id=unit_id
from unit
start with unit_id='查询条件'
CONNECT BY PRIOR unit_id=parent_id
(
unit_id NUMBER,
unit_name VARCHAR2(100),
parent_id NUMBER
);INSERT INTO UNIT
SELECT '1','LEVEL1',''
FROM DUAL
UNION
SELECT '12','LEVEL12','1'
FROM DUAL
UNION
SELECT '13','LEVEL13','1'
FROM DUAL
UNION
SELECT '121','LEVEL121','12'
FROM DUAL
UNION
SELECT '131','LEVEL131','13'
FROM DUAL
UNION
SELECT '122','LEVEL122','121'
FROM DUAL
UNION
SELECT '132','LEVEL132','131'
FROM DUAL;
SELECT LEVEL,
T.UNIT_ID
,T.UNIT_NAME
FROM UNIT T
START WITH T.UNIT_ID = 12
CONNECT BY PRIOR T.UNIT_ID = T.PARENT_ID
UNION
SELECT LEVEL,
T.UNIT_ID
,T.UNIT_NAME
FROM UNIT T
START WITH T.UNIT_ID = 12
CONNECT BY PRIOR T.PARENT_ID = T.UNIT_ID
select *
from unit
start with unit_id='查询条件'
CONNECT BY PRIOR parent_id=unit_id
用層次化查詢去做
這樣是最好的
START WITH
CONNECT BY