在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
------------------------------------华丽丽的分割线------------------------------------
------------------------------------华丽丽的分割线------------------------------------