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