透過 SQL 查詢 Oracle 樹狀結構某節點下的所有節點

• ORACLE 限定方法: START WITH ... CONNECT BY PRIOR ...
• Oracle ex:

SELECT Id
FROM Category START WITH Id=2 CONNECT BY
PRIOR Id=Parentid

透過 SQL 查詢 MSSQL 樹狀結構某節點下的所有節點

• MSSQL 似乎沒有直接的查詢語法(?), 但有 with
• MSSQL ex:

WITH n (id, name) AS
    (SELECT id, name
    FROM category
    WHERE id = 2 --父節點 id
    UNION ALL SELECT child.id, child.name
    FROM Category AS child, n
    WHERE n.id = child.parentId)

SELECT * FROM n

如果要透過 n 再查詢, 例如查詢分類樹底下所有 binder
(假設 Binder 和 Category 透過 Binder_Category 來記錄關聯性)

WITH n (id, name) AS (SELECT id, name FROM category WHERE id = 2 --父節點 id UNION ALL SELECT child.id, child.name FROM Category AS child, n WHERE n.id = child.parentId) SELECT * FROM Binder bb LEFT JOIN Binder_Category bc ON bc.binder_id = bb.id WHERE bc.cat_id IN (SELECT id FROM n)

(感謝 April 大大教學)