ORACLE 트리 구조 쿼리를 분석한 기사 1개

-- Create table
create table TREETEST
(
  id   NVARCHAR2(50),
  pid  NVARCHAR2(50),
  name NVARCHAR2(50)
)

insert into TREETEST (ID, PID, NAME) values ('1', null, '山东省');
insert into TREETEST (ID, PID, NAME) values ('2', '1', '青岛市');
insert into TREETEST (ID, PID, NAME) values ('3', '1', '烟台市');
insert into TREETEST (ID, PID, NAME) values ('4', null, '河南省');
insert into TREETEST (ID, PID, NAME) values ('5', null, '河北省');
insert into TREETEST (ID, PID, NAME) values ('6', '2', '市南区');
insert into TREETEST (ID, PID, NAME) values ('7', '2', '市北区');
insert into TREETEST (ID, PID, NAME) values ('8', '2', '即墨市');

SELECT [Column]…..
　　FEOM [Table]
　　WHERE Conditional1
　　START WITH Conditional2
　　CONNECT BY PRIOR Conditional3
　　ORDER BY [Column]

SELECT *
  FROM TREETEST t
 START WITH t.PID=1
CONNECT BY PRIOR t.ID = t.PID

SELECT t.ID, SYS_CONNECT_BY_PATH(t.NAME, '\') AS INDU_NAME
  FROM TREETEST t
 START WITH t.PID IS NULL
CONNECT BY PRIOR t.ID = t.PID

SELECT *
  FROM TREETEST t
 START WITH t.ID=8
CONNECT BY t.ID = PRIOR t.PID