2021-10-25 PostgreSQL递归查询树形结构

DROP TABLE IF EXISTS public.t_tree;
CREATE TABLE public.t_tree (
	id varchar,
	"name" varchar,
	pid varchar
);
INSERT INTO t_tree (id, name, pid) VALUES (1, 'test1', NULL);
INSERT INTO t_tree (id, name, pid) VALUES (2, 'test1-1', 1);
INSERT INTO t_tree (id, name, pid) VALUES (3, 'test1-1-1', 2);
INSERT INTO t_tree (id, name, pid) VALUES (4, 'test2', NULL);
INSERT INTO t_tree (id, name, pid) VALUES (5, 'test3', NULL);
INSERT INTO t_tree (id, name, pid) VALUES (6, 'test1-2', 1);
INSERT INTO t_tree (id, name, pid) VALUES (7, 'test1-3', 1);
INSERT INTO t_tree (id, name, pid) VALUES (8, 'test1-1-2', 2);
INSERT INTO t_tree (id, name, pid) VALUES (9, 'test1-1-3', 2);
INSERT INTO t_tree (id, name, pid) VALUES (10, 'test2-1', 4);
INSERT INTO t_tree (id, name, pid) VALUES (11, 'test2-2', 4);
INSERT INTO t_tree (id, name, pid) VALUES (12, 'test2-1-1', 10);
INSERT INTO t_tree (id, name, pid) VALUES (13, 'test3-1', 5);
INSERT INTO t_tree (id, name, pid) VALUES (14, 'test3-1-1', 13);
WITH RECURSIVE T (ID, NAME, PID, PATH, DEPTH)  AS (
    SELECT ID, NAME, PID, ARRAY[ID] AS PATH, 1 AS DEPTH
    FROM t_tree
    WHERE PID IS NULL

    UNION ALL

    SELECT  D.ID, D.NAME, D.PID, T.PATH || D.ID, T.DEPTH + 1 AS DEPTH
    FROM t_tree D
    JOIN T ON D.PID = T.ID
)
SELECT ID, NAME, PID, PATH, DEPTH FROM T
ORDER BY PATH;