sqlserver 树结构递归（向上递归和向下递归）

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15

--获取当前及以下部门 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

　　

1 2 3 4 5 6 7 8 9

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