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