PostgreSQL遞迴查詢實現樹狀結構查詢

PostgreSQL遞迴查詢實現樹狀結構查詢

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

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

下面我們造幾條測試資料

INSERT INTO DEPARTMENT(ID, NAME, PARENT_ID) VALUES(1, 'DEPARTMENT_1', NULL);
INSERT INTO DEPARTMENT(ID, NAME, PARENT_ID) VALUES(11, 'DEPARTMENT_11', 1);
INSERT INTO DEPARTMENT(ID, NAME, PARENT_ID) VALUES(12, 'DEPARTMENT_12', 1);
INSERT INTO DEPARTMENT(ID, NAME, PARENT_ID) VALUES(111, 'DEPARTMENT_111', 11);
INSERT INTO DEPARTMENT(ID, NAME, PARENT_ID) VALUES(121, 'DEPARTMENT_121', 12);
INSERT INTO DEPARTMENT(ID, NAME, PARENT_ID) VALUES(122, 'DEPARTMENT_122', 12);

其中
- 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, 1 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 + 1 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

------------------------------------華麗麗的分割線------------------------------------

CentOS 6.3環境下yum安裝PostgreSQL 9.3

PostgreSQL緩衝詳述

Windows平台編譯 PostgreSQL

Ubuntu下LAPP（Linux+Apache+PostgreSQL+PHP)環境的配置與安裝

Ubuntu上的phppgAdmin安裝及配置

CentOS平台下安裝PostgreSQL9.3

PostgreSQL配置Streaming Replication叢集

如何在CentOS 7/6.5/6.4 下安裝PostgreSQL 9.3 與 phpPgAdmin 

------------------------------------華麗麗的分割線------------------------------------

PostgreSQL 的詳細介紹：請點這裡
PostgreSQL 的：請點這裡

本文永久更新連結地址：