PostgreSQL recursive query for tree structure query and postgresql Recursion

PostgreSQL recursive query for tree structure query and postgresql Recursion

A very interesting feature found during Postgresql's use is that recursive queries can be used for results that require a tree-like structure. For example, the data structure of common company departments is similar to the following SQL statement when we design the table structure. If parent_id is NULL, it indicates the top-level node. Otherwise, it indicates the ID of the parent node.

CREATE TABLE DEPARTMENT ( ID INTEGER PRIMARY KEY, NAME VARCHAR(32), PARENT_ID INTEGER REFERENCES DEPARTMENT(ID));

Below we create several pieces of test data

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);

Where
-DEPARTMENT_1 is a top-level node with two subnodes: DEPARTMENT_11 and DEPARTMENT_12.
-The DEPARTMENT_11 node has another subnode, DEPARTMENT_111.
-The DEPARTMENT_12 node has two subnodes: DEPARTMENT_121 and DEPARTMENT_122.

The following statements are used to generate a tree structure for Recursive queries:

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 TORDER BY PATH;

ID  NAME            PARENT_ID   PATH      DEPTH1   DEPARTMENT_1                1         111  DEPARTMENT_11   1           1,11      2111 DEPARTMENT_111  11          1,11,111  312  DEPARTMENT_12   1           1,12      2121 DEPARTMENT_121  12          1,12,121  3122 DEPARTMENT_122  12          1,12,122  3

Reprinted please mark the address of this article as a link
Address: http://blog.csdn.net/kongxx/article/details/47035491

Copyright Disclaimer: This article is an original article by the blogger and cannot be reproduced without the permission of the blogger.