WITH cte_name AS (
    -- 查詢語句
)
SELECT * FROM cte_name;

WITH RECURSIVE cte_name AS (
    -- 1. 初始查詢（錨點成員 Anchor Member）
    SELECT ... FROM table WHERE ...

    UNION [ALL]

    -- 2. 遞歸查詢（遞歸成員 Recursive Member）
    SELECT ... FROM table, cte_name WHERE ...
)
SELECT * FROM cte_name;

CREATE TABLE departments (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    parent_id INTEGER REFERENCES departments(id)
);

-- 插入示例數(shù)據(jù)
INSERT INTO departments (name, parent_id) VALUES
('總公司', NULL),
('技術(shù)部', 1),
('產(chǎn)品部', 1),
('前端組', 2),
('后端組', 2),
('iOS組', 2),
('設(shè)計組', 3);

WITH RECURSIVE dept_tree AS (
    -- 錨點：找到“技術(shù)部”
    SELECT id, name, parent_id, 0 AS level
    FROM departments
    WHERE name = '技術(shù)部'

    UNION ALL

    -- 遞歸：找子部門
    SELECT d.id, d.name, d.parent_id, dt.level + 1
    FROM departments d
    INNER JOIN dept_tree dt ON d.parent_id = dt.id
)
SELECT 
    LPAD('', level * 4, ' ') || name AS hierarchy,  -- 縮進顯示層級
    id, parent_id, level
FROM dept_tree
ORDER BY level;

hierarchy        | id | parent_id | level
-----------------|----|-----------|------
技術(shù)部           | 2  | 1         | 0
    前端組       | 4  | 2         | 1
    后端組       | 5  | 2         | 1
    iOS組        | 6  | 2         | 1

WITH RECURSIVE dept_path AS (
    -- 錨點：從“后端組”開始
    SELECT id, name, parent_id, 0 AS level
    FROM departments
    WHERE name = '后端組'

    UNION ALL

    -- 遞歸：找父部門
    SELECT d.id, d.name, d.parent_id, dp.level + 1
    FROM departments d
    INNER JOIN dept_path dp ON d.id = dp.parent_id
    WHERE dp.parent_id IS NOT NULL  -- 避免 NULL 連接
)
SELECT 
    REPEAT(' → ', level) || name AS path_from_root
FROM dept_path
ORDER BY level DESC;  -- 從根到當(dāng)前節(jié)點

path_from_root
---------------------------
總公司 → 技術(shù)部 → 后端組

WITH RECURSIVE dept_limited AS (
    SELECT id, name, parent_id, 1 AS depth
    FROM departments
    WHERE parent_id IS NULL  -- 從根開始

    UNION ALL

    SELECT d.id, d.name, d.parent_id, dl.depth + 1
    FROM departments d
    INNER JOIN dept_limited dl ON d.parent_id = dl.id
    WHERE dl.depth < 3  -- 最多查3層
)
SELECT * FROM dept_limited;

WITH RECURSIVE graph_traversal AS (
    -- 錨點
    SELECT 
        id, 
        name, 
        parent_id,
        ARRAY[id] AS path,      -- 記錄已訪問節(jié)點
        1 AS depth
    FROM departments
    WHERE name = '技術(shù)部'

    UNION ALL

    -- 遞歸
    SELECT 
        d.id,
        d.name,
        d.parent_id,
        gt.path || d.id,        -- 追加當(dāng)前節(jié)點
        gt.depth + 1
    FROM departments d
    INNER JOIN graph_traversal gt ON d.parent_id = gt.id
    WHERE 
        d.id != ALL(gt.path)    -- 關(guān)鍵：當(dāng)前節(jié)點不在已訪問路徑中
        AND gt.depth < 10       -- 安全兜底
)
SELECT * FROM graph_traversal;

WITH RECURSIVE tree AS (
    -- 葉子節(jié)點（無子節(jié)點）
    SELECT 
        id,
        name,
        parent_id,
        jsonb_build_object('id', id, 'name', name, 'children', '[]'::jsonb) AS node
    FROM categories c1
    WHERE NOT EXISTS (
        SELECT 1 FROM categories c2 WHERE c2.parent_id = c1.id
    )

    UNION ALL

    -- 非葉子節(jié)點（聚合子節(jié)點）
    SELECT 
        p.id,
        p.name,
        p.parent_id,
        jsonb_build_object(
            'id', p.id,
            'name', p.name,
            'children', jsonb_agg(t.node)
        ) AS node
    FROM categories p
    INNER JOIN tree t ON t.parent_id = p.id
    GROUP BY p.id, p.name, p.parent_id
)
SELECT node 
FROM tree 
WHERE parent_id IS NULL;  -- 返回根節(jié)點

{
  "id": 1,
  "name": "電子產(chǎn)品",
  "children": [
    {
      "id": 2,
      "name": "手機",
      "children": [
        {"id": 3, "name": "iPhone", "children": []},
        {"id": 4, "name": "華為", "children": []}
      ]
    },
    {
      "id": 5,
      "name": "電腦",
      "children": [
        {"id": 6, "name": "筆記本", "children": []}
      ]
    }
  ]
}

CREATE TABLE categories (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    parent_id INTEGER REFERENCES categories(id),
    is_leaf BOOLEAN DEFAULT false  -- 是否葉子節(jié)點
);

-- 插入數(shù)據(jù)
INSERT INTO categories (name, parent_id, is_leaf) VALUES
('電子產(chǎn)品', NULL, false),
('手機', 1, false),
('iPhone', 2, true),
('華為', 2, true),
('電腦', 1, false),
('筆記本', 5, true);

WITH RECURSIVE category_tree AS (
    -- 錨點：根類目
    SELECT 
        id, 
        name, 
        parent_id,
        name::TEXT AS full_path,  -- 路徑字符串
        1 AS level
    FROM categories
    WHERE name = '電子產(chǎn)品'

    UNION ALL

    -- 遞歸：拼接路徑
    SELECT 
        c.id,
        c.name,
        c.parent_id,
        ct.full_path || ' > ' || c.name,  -- 路徑拼接
        ct.level + 1
    FROM categories c
    INNER JOIN category_tree ct ON c.parent_id = ct.id
)
SELECT 
    full_path,
    id,
    level
FROM category_tree
WHERE is_leaf = true;  -- 只查葉子節(jié)點

full_path                   | id | level
----------------------------|----|------
電子產(chǎn)品 > 手機 > iPhone   | 3  | 3
電子產(chǎn)品 > 手機 > 華為     | 4  | 3
電子產(chǎn)品 > 電腦 > 筆記本   | 6  | 3

from sqlalchemy import text
from sqlalchemy.orm import sessionmaker

def get_dept_tree(session, root_name):
    query = text("""
        WITH RECURSIVE dept_tree AS (
            SELECT id, name, parent_id, 0 AS level
            FROM departments
            WHERE name = :root_name
            UNION ALL
            SELECT d.id, d.name, d.parent_id, dt.level + 1
            FROM departments d
            INNER JOIN dept_tree dt ON d.parent_id = dt.id
        )
        SELECT * FROM dept_tree ORDER BY level;
    """)
    
    result = session.execute(query, {"root_name": root_name})
    return result.fetchall()

# 使用
with Session() as session:
    tree = get_dept_tree(session, "技術(shù)部")
    for row in tree:
        print(f"{'  ' * row.level}{row.name}")

-- 對 parent_id 建索引（遞歸連接的關(guān)鍵）
CREATE INDEX idx_departments_parent_id ON departments(parent_id);

-- 如果常按 name 查詢根節(jié)點
CREATE INDEX idx_departments_name ON departments(name);

EXPLAIN (ANALYZE, BUFFERS)
WITH RECURSIVE ... ;  -- 你的遞歸查詢

ALTER TABLE categories ADD COLUMN path TEXT; -- 如 "/1/2/3/"

-- 查詢“手機”下所有子類目
SELECT * FROM categories 
WHERE path LIKE '/1/2/%';

CREATE TABLE category_closure (
    ancestor_id INT,
    descendant_id INT,
    depth INT
);

-- 查詢“手機”(id=2)的所有后代
SELECT c.* 
FROM categories c
JOIN category_closure cl ON c.id = cl.descendant_id
WHERE cl.ancestor_id = 2;