在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,PARENT_ID) VALUES(11,'DEPARTMENT_11',1);
INSERT INTO DEPARTMENT(ID,PARENT_ID) VALUES(12,'DEPARTMENT_12',PARENT_ID) VALUES(111,'DEPARTMENT_111',11);
INSERT INTO DEPARTMENT(ID,PARENT_ID) VALUES(121,'DEPARTMENT_121',12);
INSERT INTO DEPARTMENT(ID,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,PARENT_ID,PATH,DEPTH)  AS (
    SELECT 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,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,122  3

转载请以链接形式标明本文地址
本文地址：http://www.jb51.cc/article/p-ubolklpt-nt.html