SQL 遞迴查詢跨資料庫實戰：SQL Server CTE、Oracle CONNECT BY、MySQL 存儲函數

場景：樹狀結構上下游查詢

表結構 `Tree(ID, Name, ParentId)`，資料：

ID  Name  ParentId
1   L1    0
2   L2    1
3   L3    2
4   L4    3

• 下游查詢：給定節點，找所有子孫 (ParentId 遞迴)。
• 上游查詢：給定節點，找所有祖先 (ID 遞迴)。

SQL Server (2005+)：遞迴 CTE

下游 (子孫)

WITH tmpTree AS (
    SELECT * FROM Tree WHERE Id = 2          -- 錨點：起始節點
    UNION ALL
    SELECT p.* FROM tmpTree
    INNER JOIN Tree p ON p.ParentId = tmpTree.Id  -- 遞迴：找孩子
)
SELECT * FROM tmpTree;

上游 (祖先)

WITH tmpTree AS (
    SELECT * FROM Tree WHERE Id = 2
    UNION ALL
    SELECT p.* FROM tmpTree
    INNER JOIN Tree p ON p.Id = tmpTree.ParentId  -- 遞迴：找父親
)
SELECT * FROM tmpTree;

SQL Server 2008+ 新增：HierarchyID 原生樹狀型別

-- 表結構調整：新增 Hid HIERARCHYID，移除 ParentId
-- 資料範例：
-- Hid            Id  Name
-- 0x             1   L1
-- 0x58           2   L2
-- 0x5840         3   L3
-- 0x584040       4   L4

-- 取得所有層級
SELECT *, Hid.GetLevel() AS Lvl FROM Tree2;

-- 取得節點 2 的所有下游
DECLARE @Parent HIERARCHYID = (SELECT Hid FROM Tree2 WHERE Id = 2);
SELECT *, Hid.GetLevel() AS Lvl FROM Tree2
WHERE Hid.IsDescendantOf(@Parent) = 1;

-- 取得節點 3 的所有上游
DECLARE @Child HIERARCHYID = (SELECT Hid FROM Tree2 WHERE Id = 3);
SELECT *, Hid.GetLevel() AS Lvl FROM Tree2
WHERE @Child.IsDescendantOf(Hid) = 1;

Oracle：CONNECT BY

下游

SELECT * FROM Tree
START WITH Id = 2
CONNECT BY PRIOR Id = ParentId;

上游

SELECT * FROM Tree
START WITH Id = 2
CONNECT BY Id = PRIOR ParentId;

MySQL (8.0 前)：存儲函數 + FIND_IN_SET 模擬遞迴

MySQL 8.0+ 已支援遞迴 CTE，以下為 5.7/8.0 相容寫法。

通用函數：取得指定節點的所有上/下游 ID 清單 (逗號分隔)

CREATE FUNCTION `getChildLst`(rootId INT, direction INT) RETURNS VARCHAR(1000) CHARSET utf8
DETERMINISTIC
BEGIN
    DECLARE sTemp VARCHAR(5000) DEFAULT '$';
    DECLARE sTempChd VARCHAR(1000);

    SET sTempChd = CAST(rootId AS CHAR);

    IF direction = 1 THEN -- 下游
        WHILE sTempChd IS NOT NULL DO
            SET sTemp = CONCAT(sTemp, ',', sTempChd);
            SELECT GROUP_CONCAT(Id) INTO sTempChd
            FROM Tree WHERE FIND_IN_SET(ParentId, sTempChd) > 0;
        END WHILE;
    ELSEIF direction = 2 THEN -- 上游
        WHILE sTempChd IS NOT NULL DO
            SET sTemp = CONCAT(sTemp, ',', sTempChd);
            SELECT GROUP_CONCAT(ParentId) INTO sTempChd
            FROM Tree WHERE FIND_IN_SET(Id, sTempChd) > 0;
        END WHILE;
    END IF;

    RETURN sTemp;
END;

查詢用法

-- 下游：節點 1 的所有子孫
SELECT * FROM Tree WHERE FIND_IN_SET(Id, getChildLst(1, 1));

-- 上游：節點 1 的所有祖先
SELECT * FROM Tree WHERE FIND_IN_SET(Id, getChildLst(1, 2));

MySQL 8.0+ 標準遞迴 CTE (推薦)

-- 下游
WITH RECURSIVE cte AS (
    SELECT * FROM Tree WHERE Id = 1
    UNION ALL
    SELECT t.* FROM cte JOIN Tree t ON t.ParentId = cte.Id
)
SELECT * FROM cte;

-- 上游
WITH RECURSIVE cte AS (
    SELECT * FROM Tree WHERE Id = 4
    UNION ALL
    SELECT t.* FROM cte JOIN Tree t ON t.Id = cte.ParentId
)
SELECT * FROM cte;

效能與限制對比

最佳實踐建議

1. 欄位索引：`ParentId`、 `Id` 必須建立索引。
2. 避免循環：資料層面約束 (觸發器/應用層) 防止 `ParentId` 形成環。
3. 大樹分頁：遞迴查詢可能回傳大量行，前端需分頁或懶加載。
4. 物化路徑：頻繁查詢祖先/子孫時，考慮冗余 `Path` 欄位 (如 `/1/2/3/`) 或 `HierarchyID`，以空間換時間。

總結

跨資料庫遞迴查詢：**SQL Server/Oracle/MySQL 8.0+ 皆有原生標準語法 (CTE/CONNECT BY)**，優先使用。MySQL 5.7 僅能靠函數模擬，效能與長度受限，建議規劃升級。`HierarchyID` (SQL Server) 或 `Path` 物化路徑是高頻樹狀查詢的進階優化方向。