SQL 遞歸查詢樹形結構：從父節點查詢所有子孫節點

場景：表結構 `id, parent_id, name`，`parent_id=NULL` 為根節點。需查詢節點 1 的所有後代（子、孫、曾孫...）。

標準 SQL:1999 遞歸 CTE (通用表表達式)

支援：PostgreSQL, SQL Server, Oracle 11g+, MySQL 8.0+, SQLite, MariaDB 10.2+。

WITH RECURSIVE descendants AS (
    -- 錨點：根節點
    SELECT id, parent_id, name, 1 AS level
    FROM tree_table
    WHERE id = 1
    
    UNION ALL
    
    -- 遞歸：關聯子節點
    SELECT t.id, t.parent_id, t.name, d.level + 1
    FROM tree_table t
    INNER JOIN descendants d ON t.parent_id = d.id
)
SELECT id, name, level
FROM descendants
WHERE id != 1  -- 排除根節點自身
ORDER BY level, id;

MySQL 5.7 / 老版本替代方案

方案一：存儲過程迴圈

CREATE PROCEDURE GetDescendants(IN rootId INT)
BEGIN
    CREATE TEMPORARY TABLE tmp (id INT PRIMARY KEY, level INT);
    INSERT INTO tmp SELECT id, 1 FROM tree_table WHERE id = rootId;
    
    REPEAT
        INSERT IGNORE INTO tmp 
        SELECT t.id, tmp.level + 1
        FROM tree_table t
        INNER JOIN tmp ON t.parent_id = tmp.id
        WHERE tmp.level = (SELECT MAX(level) FROM tmp);
    UNTIL ROW_COUNT() = 0 END REPEAT;
    
    SELECT t.* FROM tree_table t INNER JOIN tmp ON t.id = tmp.id WHERE t.id != rootId;
    DROP TEMPORARY TABLE tmp;
END;

方案二：應用層遞歸 (C# 示例)

public List GetDescendants(int rootId) {
    var result = new List();
    var queue = new Queue();
    queue.Enqueue(rootId);
    
    while (queue.Count > 0) {
        int pid = queue.Dequeue();
        var children = db.Query("SELECT * FROM tree_table WHERE parent_id = @pid", new { pid });
        foreach (var c in children) {
            result.Add(c);
            queue.Enqueue(c.Id);
        }
    }
    return result;
}

性能優化建議

• 為 `parent_id` 創建索引：`CREATE INDEX idx_parent ON tree_table(parent_id);`
• 若頻繁查詢全樹，考慮**閉包表** 或 **物化路徑** 存儲冗余路徑信息。
• 大數據量避免應用層 N+1 查詢，優先用 CTE 或存儲過程在數據庫端完成。

參考詳解：MySQL 遞歸查詢多種實現方式