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

------------------------------------华丽丽的分割线------------------------------------

------------------------------------华丽丽的分割线------------------------------------

PostgreSQL 的详细介绍:请点这里
PostgreSQL 的下载地址:请点这里

相关推荐