postgre实现树状结构查询

在开发过程中，经常使用的树状结构，比如下图功能

在上图这个功能中，主要是使用了递归查询，在postgre中内置了递归函数，下面就举个例子，来实现这种结构的查询（以postgresql为例）。

1、首先，创建一张表格，表格中包含三个字段，分别是主键id，名称name和父节点parent_id

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

2、插入几条测试数据

INSERT INTO TREE_TEST(ID, NAME, PARENT_ID) VALUES(1, 'TREE_1', NULL);INSERT INTO TREE_TEST(ID, NAME, PARENT_ID) VALUES(11, 'TREE_11', 1);INSERT INTO TREE_TEST(ID, NAME, PARENT_ID) VALUES(12, 'TREE_12', 1);INSERT INTO TREE_TEST(ID, NAME, PARENT_ID) VALUES(13, 'TREE_13', 1);INSERT INTO TREE_TEST(ID, NAME, PARENT_ID) VALUES(111, 'TREE_111', 11);INSERT INTO TREE_TEST(ID, NAME, PARENT_ID) VALUES(121, 'TREE_121', 12);INSERT INTO TREE_TEST(ID, NAME, PARENT_ID) VALUES(122, 'TREE_122', 12);INSERT INTO TREE_TEST(ID, NAME, PARENT_ID) VALUES(131, 'TREE_131', 13);INSERT INTO TREE_TEST(ID, NAME, PARENT_ID) VALUES(132, 'TREE_132', 13);INSERT INTO TREE_TEST(ID, NAME, PARENT_ID) VALUES(133, 'TREE_133', 13);

3、假如我们需要查询TREE_13和该数据所有的子节点数据，sql可以如下

WITH RECURSIVE T(ID,NAME,PARENT_ID) AS(SELECT ID,NAME,PARENT_ID FROM TREE_TEST WHERE ID =13UNION ALL  SELECT T1.ID,T1.NAME,T1.PARENT_ID   FROM TREE_TEST T1 JOIN T ON T1.PARENT_ID=T.ID  )SELECT ID,NAME,PARENT_ID FROM T

这句话执行的步骤是

    ->先执行语句SELECT ID,NAME,PARENT_ID FROM TREE_TEST WHERE ID =13

    ->把上一步执行的结果作为表T，然后用该表T与TREE_TEST实现UNION ALL查询，并把查询的结果作为T，继续这种UNION ALL，直到查询完所有的子级叶子节点

    ->执行最后面一句sql：SELECT ID,NAME,PARENT_ID FROM T

查询结果如下：

idname       parent_id13TREE_13        1131TREE_13113132TREE_13213133TREE_13313

4、我们也可以查询出所有的数据父节点id，该数据的路径以及深度，查询sql如下

WITH RECURSIVE T (ID, NAME, PARENT_ID, PATH, DEPTH)  AS (    SELECT ID, NAME, PARENT_ID, ARRAY[ID] AS PATH, 1 AS DEPTH    FROM TREE_TEST    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 TREE_TEST D    JOIN T ON D.PARENT_ID = T.ID    )    SELECT ID, NAME, PARENT_ID, PATH, DEPTH FROM TORDER BY PATH;

查询结果如下：

idnameparent_idpathdepth1TREE_1{1}111TREE_111{1,11}2111TREE_11111{1,11,111}312TREE_121{1,12}2121TREE_12112{1,12,121}3122TREE_12212{1,12,122}313TREE_131{1,13}2131TREE_13113{1,13,131}3132TREE_13213{1,13,132}3133TREE_13313{1,13,133}3

5、假如我们需要某个节点的全路径，如TREE_111的全路径为：TREE_1/TREE_11/TREE_111，如何写sql进行查询呢?

WITH RECURSIVE cte AS (SELECTA . ID,A . NAME,CAST (A . NAME AS VARCHAR(4000)) AS name_full_pathFROMtree_test AWHEREA .parent_id is nullUNION ALLSELECTK . ID,K . NAME,CAST (C .name_full_path || '/' || K . NAME AS VARCHAR (4000)) AS name_full_pathFROMtree_test KINNER JOIN cte C ON C . ID = K .parent_id) SELECT*FROMcte;select * from tree_test;

查询结果如下：

id      name            name_full_path

1TREE_1        TREE_111TREE_11        TREE_1/TREE_1112TREE_12        TREE_1/TREE_1213TREE_13        TREE_1/TREE_13111TREE_111TREE_1/TREE_11/TREE_111121TREE_121TREE_1/TREE_12/TREE_121122TREE_122TREE_1/TREE_12/TREE_122131TREE_131TREE_1/TREE_13/TREE_131132TREE_132TREE_1/TREE_13/TREE_132133TREE_133TREE_1/TREE_13/TREE_133