SQL Server 递归查询上级或下级组织数据（上下级数据通用查询语法）

查询上级组织数据：

  WITH OCTE AS
  (
  SELECT O.ID,O.ParentId,O.Name,0 AS LVL FROM IOV_Users U LEFT JOIN IOV_Organization O ON U.OrgId=O.ID 
  WHERE U.Id=32082 
  UNION ALL 
  SELECT O.ID,O.ParentId,O.Name,C.LVL+1 FROM OCTE C INNER JOIN IOV_Organization O ON C.ParentId=O.ID 
  )
  SELECT * FROM OCTE

查询下级组织数据：

  WITH OCTE AS
  (
  SELECT O.ID,O.ParentId,O.Name,0 AS LVL FROM IOV_Users U LEFT JOIN IOV_Organization O ON U.OrgId=O.ID 
  WHERE U.Id=32082 
  UNION ALL 
  SELECT O.ID,O.ParentId,O.Name,C.LVL+1 FROM OCTE C INNER JOIN IOV_Organization O ON C.ID=O.ParentId 
  )
  SELECT * FROM OCTE

 

转载于:https://www.cnblogs.com/fjzhang/p/10026745.html