sqlserver 樹結構遞迴（向上遞迴和向下遞迴）

標籤：

--擷取當前及以下部門Create proc GetCurrentAndUnderOrg@orgId intasbegin    WITH cte     AS    (        SELECT * ,0 AS level FROM Static_Organ  WHERE [email protected]        UNION ALL        SELECT g.*,level+1 FROM Static_Organ g INNER JOIN cte              ON g.ParentOrgan=cte.OrganID    )    SELECT * FROM cteend

　　

 --向上遞迴   WITH cte     AS    (        SELECT * ,LevelIndex AS level FROM EL_Departments  WHERE ID=170        UNION ALL        SELECT g.*,g.LevelIndex AS level FROM EL_Departments g INNER JOIN cte              ON g.Id=cte.ParentId    )

　　向上遞迴和向下遞迴，其實就是反一反。以上語句因為要用到with as ，所以資料庫要sqlserver2005以上。

sqlserver 樹結構遞迴（向上遞迴和向下遞迴）