postgresql实现树状查询

postgresql实现树状查询

先创建一张表TREE_TEST，表中有三个字段，分别是id，name和parent_id

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

 

然后插入几条测试数据

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

 

使用postgre中的recursive实现递归查询

WITH RECURSIVE T(ID,NAME,PARENT_ID) AS(
    SELECT ID,NAME,PARENT_ID FROM TREE_TEST WHERE ID =13
    UNION 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

这句话执行的步骤是：

1、先执行语句SELECT ID,NAME,PARENT_ID FROM TREE_TEST WHERE ID =13

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

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

该语句是查询出id为13和13下所有的子集，查询结果如下：

id    name    parent_id
13    TREE_13        1
131    TREE_131    13
132    TREE_132    13
133    TREE_133    13

当然也可以把某个子级的深度查询出来，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 T
ORDER BY PATH;

 

该语句查询结果如下：

id        name        parent_id    path                depth
1        TREE_1                            {1}                    1
11        TREE_11        1                {1,11}                2
111        TREE_111    11            {1,11,111}        3
12        TREE_12        1            {1,12}                2
121        TREE_121    12            {1,12,121}        3
122        TREE_122    12            {1,12,122}        3
13        TREE_13        1            {1,13}                2
131        TREE_131    13            {1,13,131}        3
132        TREE_132    13            {1,13,132}        3
133        TREE_133    13            {1,13,133}        3

 

 

 

 

 

转载于:https://my.oschina.net/u/858241/blog/1058104