Postgresql 递归查询树

WITH RECURSIVE T (ID, ar_id,hold_ent_name,hold_entid,open_ticket_date,ticket_amt,ticket_cash_date,hold_date,ticket_status, PARENT_ID, PATH, DEPTH) AS (
SELECT ID, ar_id, hold_ent_name,hold_entid,open_ticket_date,ticket_amt,ticket_cash_date,hold_date,ticket_status, PARENT_ID, ARRAY[ID] AS PATH, 1 AS DEPTH
FROM fin_user.t_financing_ticket
WHERE PARENT_ID IS NULL and id='10001301'
UNION ALL
SELECT D.ID,D.ar_id,D.hold_ent_name,D.hold_entid,D.open_ticket_date,D.ticket_amt,D.ticket_cash_date,D.hold_date,D.ticket_status, D.PARENT_ID, T.PATH || D.ID, T.DEPTH + 1 AS DEPTH
FROM fin_user.t_financing_ticket D
JOIN T ON D.PARENT_ID = T.ID
)
SELECT ID,ar_id, hold_ent_name,hold_entid,open_ticket_date,ticket_amt,ticket_cash_date,hold_date,ticket_status, PARENT_ID, PATH, DEPTH FROM T
ORDER BY DEPTH;

在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

转载请以链接形式标明本文地址 

转载于:https://www.cnblogs.com/wanglijun/p/8709298.html