SQL Server 递归查询上级或下级组织数据

• 查询下级(包括自身)

WITH TempTable AS (
                SELECT ID, Name, ParentID FROM table WHERE ID in (25) 
                UNION ALL 
                SELECT a.ID,a.Name,a.ParentID 
                FROM TempTable 
                JOIN table a ON TempTable.ID = a.ParentID
            ) SELECT * FROM TempTable

• 查询下级(不包括自身)

with cte as
(select * from table where ParentID=25 and IsDeleted=0
union all
select a.* from table a join cte b on a.ParentID=b.ID where a.IsDeleted=0)
select * from cte

• 查询上级（不包括自身）

WITH OCTE AS
  (
  SELECT O.ID,O.ParentID,O.Name FROM table U LEFT JOIN table O ON U.ParentID=O.ID 
  WHERE U.ID=25 
  UNION ALL 
  SELECT O.ID,O.ParentID,O.Name FROM OCTE C INNER JOIN table O ON C.ParentID=O.ID 
  )
  SELECT * FROM OCTE

• 查询上级（包括自身）

WITH T
AS( 
    SELECT * FROM table WHERE Id=25
    UNION ALL 
    SELECT a.*  
    FROM table a INNER JOIN T ON a.Id=T.ParentId  
) 
SELECT * FROM T