sqlserver 树结构递归(向上递归和向下递归)

--获取当前及以下部门
Create proc GetCurrentAndUnderOrg
@orgId int
as
begin
    WITH cte 
    AS
    (
        SELECT * ,0 AS level FROM Static_Organ  WHERE OrganID=@orgId
        UNION ALL
        SELECT g.*,level+1 FROM Static_Organ g INNER JOIN cte 
             ON g.ParentOrgan=cte.OrganID
    )
    SELECT * FROM cte
end

  

 --向上递归
   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以上。

posted @ 2014-12-30 15:03  撞上安全岛  阅读(8094)  评论(0编辑  收藏  举报