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 =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

这句话执行的步骤是

    ->先执行语句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

查询结果如下：

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

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

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

WITH RECURSIVE cte AS (
	SELECT
		A . ID,
		A . NAME,
		CAST (A . NAME AS VARCHAR(4000)) AS name_full_path
	FROM
		tree_test A
	WHERE
		A .parent_id is null
	UNION ALL
		SELECT
			K . ID,
			K . NAME,
			CAST (
				C .name_full_path || '/' || K . NAME AS VARCHAR (4000)
			) AS name_full_path
		FROM
			tree_test K
		INNER JOIN cte C ON C . ID = K .parent_id
) SELECT
	*
FROM
	cte;

select * from tree_test;

查询结果如下：

id      name            name_full_path

1	TREE_1	        TREE_1
11	TREE_11	        TREE_1/TREE_11
12	TREE_12	        TREE_1/TREE_12
13	TREE_13	        TREE_1/TREE_13
111	TREE_111	TREE_1/TREE_11/TREE_111
121	TREE_121	TREE_1/TREE_12/TREE_121
122	TREE_122	TREE_1/TREE_12/TREE_122
131	TREE_131	TREE_1/TREE_13/TREE_131
132	TREE_132	TREE_1/TREE_13/TREE_132
133	TREE_133	TREE_1/TREE_13/TREE_133