Oracle遞迴查詢

標籤：

建立表

 建表語句：

CREATE TABLE SC_DISTRICT(  ID         NUMBER(10)                  NOT NULL,  PARENT_ID  NUMBER(10),  NAME       VARCHAR2(255 BYTE)          NOT NULL);ALTER TABLE SC_DISTRICT ADD (  CONSTRAINT SC_DISTRICT_PK PRIMARY KEY (ID));ALTER TABLE SC_DISTRICT ADD (  CONSTRAINT SC_DISTRICT_R01  FOREIGN KEY (PARENT_ID)  REFERENCES SC_DISTRICT (ID));

 插入資料:

INSERT INTO SC_DISTRICT(ID,NAME) VALUES(1,‘四川省‘);INSERT INTO SC_DISTRICT(ID,PARENT_ID,NAME) VALUES(2,1,‘巴中市‘);INSERT INTO SC_DISTRICT(ID,PARENT_ID,NAME) VALUES(3,1,‘達州市‘); INSERT INTO SC_DISTRICT(ID,PARENT_ID,NAME) VALUES(4,2,‘巴州區‘);INSERT INTO SC_DISTRICT(ID,PARENT_ID,NAME) VALUES(5,2,‘通江縣‘);INSERT INTO SC_DISTRICT(ID,PARENT_ID,NAME) VALUES(6,2,‘平昌縣‘);INSERT INTO SC_DISTRICT(ID,PARENT_ID,NAME) VALUES(7,3,‘通川區‘);INSERT INTO SC_DISTRICT(ID,PARENT_ID,NAME) VALUES(8,3,‘宣漢縣‘);INSERT INTO SC_DISTRICT(ID,PARENT_ID,NAME) VALUES(9,8,‘塔河鄉‘);INSERT INTO SC_DISTRICT(ID,PARENT_ID,NAME) VALUES(10,8,‘三河鄉‘);INSERT INTO SC_DISTRICT(ID,PARENT_ID,NAME) VALUES(11,8,‘胡家鎮‘);INSERT INTO SC_DISTRICT(ID,PARENT_ID,NAME) VALUES(12,8,‘南壩鎮‘); INSERT INTO SC_DISTRICT(ID,PARENT_ID,NAME) VALUES(13,6,‘大寨鄉‘);INSERT INTO SC_DISTRICT(ID,PARENT_ID,NAME) VALUES(14,6,‘響灘鎮‘);INSERT INTO SC_DISTRICT(ID,PARENT_ID,NAME) VALUES(15,6,‘龍崗鎮‘);INSERT INTO SC_DISTRICT(ID,PARENT_ID,NAME) VALUES(16,6,‘白衣鎮‘);

產生表如下：

• 查詢某節點的所有子孫節點

查詢巴中市下面的所有行政組織(結果包含當前節點):SELECT *FROM SC_DISTRICTSTART WITH NAME=‘巴中市‘CONNECT BY PRIOR ID=PARENT_ID查詢結果:ID    PARENT_ID      NAME2    1            巴中市4    2            巴州區5    2            通江縣6    2            平昌縣13    6            大寨鄉14    6            響灘鎮15    6            龍崗鎮16    6            白衣鎮

• 查詢指定節點的遞迴根節點

查詢響灘鎮鎮所屬的市：SELECT ID,   PARENT_ID,      NAME,            CONNECT_BY_ROOT(ID)   CITY_ID,       CONNECT_BY_ROOT(NAME) CITY_NAMEFROM   SC_DISTRICTWHERE  NAME=‘響灘鎮‘        START WITH PARENT_ID=1       CONNECT BY PRIOR ID=PARENT_ID查詢結果：ID        PARENT_ID    NAME    CITY_ID    CITY_NAME14        6            響灘鎮  2          巴中市

• CONNECT BY子句偽列的應用

LEVEL:查詢節點層次，從1開始。CONNECT_BY_ISLEAF：查詢節點是否是葉子節點，是則為1，不是則為0SELECT   ID,   NAME,   PARENT_ID,   LEVEL,   CONNECT_BY_ISLEAFFROM     SC_DISTRICT         START WITH NAME=‘巴中市‘         CONNECT BY PRIOR ID=PARENT_ID          ORDER BY ID;查詢結果：ID    NAME    PARENT_ID    LEVEL    CONNECT_BY_ISLEAF2    巴中市    1    1    04    巴州區    2    2    15    通江縣    2    2    16    平昌縣    2    2    013    大寨鄉    6    3    114    響灘鎮    6    3    115    龍崗鎮    6    3    116    白衣鎮    6    3    1

• 查詢遞迴路徑

查詢巴中市下行政組織遞迴路徑SELECT  ID,     NAME,   PARENT_ID,        SUBSTR(SYS_CONNECT_BY_PATH(NAME,‘->‘),3)   NAME_PATHFROM    SC_DISTRICT        START   WITH NAME=‘巴中市‘        CONNECT BY PRIOR ID=PARENT_ID        查詢結果：ID    NAME    PARENT_ID    NAME_PATH2    巴中市    1    巴中市4    巴州區    2    巴中市->巴州區5    通江縣    2    巴中市->通江縣6    平昌縣    2    巴中市->平昌縣13    大寨鄉    6    巴中市->平昌縣->大寨鄉14    響灘鎮    6    巴中市->平昌縣->響灘鎮15    龍崗鎮    6    巴中市->平昌縣->龍崗鎮16    白衣鎮    6    巴中市->平昌縣->白衣鎮

Oracle遞迴查詢