PostgreSQL递归查询实现树状结构查询

PostgreSQL递归查询实现树状结构查询 2016年7月22日不静之心未分类

http://blog.csdn.net/kongxx/article/details/47035491

在Postgresql的使用过程中发现了一个很有意思的功能，就是对于需要类似于树状结构的结果可以使用递归查询实现。比如说我们常用的公司部门这种数据结构，一般我们设计表结构的时候都是类似下面的SQL，其中parent_id为NULL时表示顶级节点，否则表示上级节点ID。

Sql代码

CREATE TABLE DEPARTMENT (

ID INTEGER PRIMARY KEY,

NAME VARCHAR(32),

PARENT_ID INTEGER REFERENCES DEPARTMENT(ID)

下面我们造几条测试数据

Sql代码

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。

下面是递归查询生成树状结构查询语句

Sql代码

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
转载请以链接形式标明本文地址
本文地址：http://blog.csdn.net/kongxx/article/details/47035491