Implementation of tree structure query with PostgreSQL recursive query

A very interesting feature found in the use of PostgreSQL is that recursive queries are implemented for results that require similar tree-like structures. For example, we commonly used the company department of this data structure, generally we design the table structure is similar to the following SQL, where parent_id is null when the top node is represented, otherwise the ancestor node ID.

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

Let's build a few test data.

 INSERT  into DEPARTMENT (ID, NAME, parent_id) VALUES(1, ' department_1 ' , NULL); INSERT  into DEPARTMENT (ID, NAME, parent_id) VALUES(One, ' Department_ One ', 1); INSERT  into DEPARTMENT (ID, NAME, parent_id) VALUES(  Department_12 ', 1); INSERT  into DEPARTMENT (ID, NAME, parent_id) VALUES(111, ' department_111 ' , one); INSERT  into DEPARTMENT (ID, NAME, parent_id) VALUES(121, ' DEPARTMENT _121 ', (); INSERT  into DEPARTMENT (ID, NAME, parent_id) VALUES(122, ' department_122 ', ();

which
-Department_1 is a top-level node with two child nodes? Department_11 and? Department_12.
-Department_11 node has another child node? department_111.
?-Department_12 node has two child nodes? department_121 and? department_122.?

The following is a recursive query generation tree structure query statement

With RECURSIVE T (ID, NAME, parent_id, PATH, DEPTH) as (SELECTID, NAME, parent_id, Array[id] asPATH,1  asDEPTH fromDEPARTMENTWHEREparent_id is NULL    UNION  All    SELECTD.id, D.name, d.parent_id, T.path | | D.id, T.depth +1  asDEPTH fromDEPARTMENT DJOINT ond.parent_id = t.id)SELECTID, NAME, parent_id, PATH, DEPTH fromTORDER  byPATH;

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

Please indicate this address in the form of a link.
This address: http://blog.csdn.net/kongxx/article/details/47035491

Copyright NOTICE: This article for Bo Master original article, without Bo Master permission not reproduced.

Implementation of tree structure query with PostgreSQL recursive query