[TOC] ## 概述 在 PostgreSQL 中实现树状结构可以使用递归查询和 CTE（公共表表达式）。 ## 示例 ```sql CREATE TABLE mytable ( id SERIAL PRIMARY KEY, name VARCHAR(50) NOT NULL, parent_id INTEGER REFERENCES mytable(id) ); -- 插入sql INSERT INTO "public"."mytable" ("id", "name", "parent_id") VALUES (1, 'aaa', NULL); INSERT INTO "public"."mytable" ("id", "name", "parent_id") VALUES (2, 'bbb', 1); INSERT INTO "public"."mytable" ("id", "name", "parent_id") VALUES (3, 'ccc', NULL); INSERT INTO "public"."mytable" ("id", "name", "parent_id") VALUES (4, 'ddd', 2); -- 查询 WITH RECURSIVE tree AS ( SELECT id, name, parent_id, 0 as level FROM mytable WHERE id = 1 UNION ALL SELECT m.id, m.name, m.parent_id, level + 1 FROM mytable m INNER JOIN tree t ON m.parent_id = t.id ) SELECT id,level,LPAD('->', level*2, '->') || name as name FROM tree ORDER BY id; // 输出 id level name 1 0 aaa 2 1 ->bbb 4 2 ->->ddd // 删除时,父表时,需要递归删除 WITH RECURSIVE tree AS ( SELECT id, parent_id FROM mytable WHERE id = 1 UNION ALL SELECT m.id, m.parent_id FROM mytable m INNER JOIN tree t ON m.parent_id = t.id ) DELETE FROM mytable WHERE id IN (SELECT id FROM tree); ```