PostgreSQL遞歸查詢實作樹狀結構查詢

在Postgresql的使用過程中發現了一個很有意思的功能，就是對于需要類似于樹狀結構的結果可以使用遞歸查詢實作。比如說我們常用的公司部門這種資料結構，一般我們設計表結構的時候都是類似下面的SQL，其中parent_id為NULL時表示頂級節點，否則表示上級節點ID。

CREATE TABLE DEPARTMENT (
 ID INTEGER PRIMARY KEY,
 NAME VARCHAR(),
 PARENT_ID INTEGER REFERENCES DEPARTMENT(ID)
);
           

下面我們造幾條測試資料

INSERT INTO DEPARTMENT(ID, NAME, PARENT_ID) VALUES(, 'DEPARTMENT_1', NULL);
INSERT INTO DEPARTMENT(ID, NAME, PARENT_ID) VALUES(, 'DEPARTMENT_11', );
INSERT INTO DEPARTMENT(ID, NAME, PARENT_ID) VALUES(, 'DEPARTMENT_12', );
INSERT INTO DEPARTMENT(ID, NAME, PARENT_ID) VALUES(, 'DEPARTMENT_111', );
INSERT INTO DEPARTMENT(ID, NAME, PARENT_ID) VALUES(, 'DEPARTMENT_121', );
INSERT INTO DEPARTMENT(ID, NAME, PARENT_ID) VALUES(, 'DEPARTMENT_122', );
           

其中

- DEPARTMENT_1是頂級節點，它有兩個子節點​DEPARTMENT_11和​DEPARTMENT_12。

- DEPARTMENT_11節點又有一個子節點​DEPARTMENT_111。

​- DEPARTMENT_12節點有兩個子節點​DEPARTMENT_121和​DEPARTMENT_122。​

下面是遞歸查詢生成樹狀結構查詢語句

WITH RECURSIVE T (ID, NAME, PARENT_ID, PATH, DEPTH)  AS (
    SELECT ID, NAME, PARENT_ID, ARRAY[ID] AS PATH,  AS DEPTH
    FROM DEPARTMENT
    WHERE PARENT_ID IS NULL

    UNION ALL

    SELECT  D.ID, D.NAME, D.PARENT_ID, T.PATH || D.ID, T.DEPTH +  AS DEPTH
    FROM DEPARTMENT D
    JOIN T ON D.PARENT_ID = T.ID
    )
    SELECT ID, NAME, PARENT_ID, PATH, DEPTH FROM T
ORDER BY PATH;
           

ID  NAME            PARENT_ID   PATH      DEPTH
1   DEPARTMENT_1                1         1
11  DEPARTMENT_11   1           1,11      2
111 DEPARTMENT_111  11          1,11,111  3
12  DEPARTMENT_12   1           1,12      2
121 DEPARTMENT_121  12          1,12,121  3
122 DEPARTMENT_122  12          1,12,122  3
           

轉載請以連結形式标明本文位址

本文位址：http://blog.csdn.net/kongxx/article/details/47035491