SQL 用;with 由所有的子节点查询到树结构中所有父节点

1、所有的子节点查询到树结构中所有父节点

RETURNS @Tree Table(PID int,FID int ,Name VARCHAR(300))
as
begin 
--DECLARE @ID VARCHAR(3)
--SET @ID = '16'
;with rolDepList as
(
  select PID,FID,Name from tbDepList as b where exists(select PID from tbUserDep where DepID=b.PID and UserID = @ID) AND (CanUse = 1)
  union all
  select a.PID,a.FID,a.Name from tbDepList  a join rolDepList b on a.pid=b.fid
)
 Insert @Tree select distinct(PID),FID,Name from rolDepList
--'select distinct(PID),FID,Name from rolDepList 

Return

end 

函数调用：

select PID,FID,Name from dbo.GetRolDep(16)

2、获取子节点的所有父节点集合

DECLARE @ID VARCHAR(8)
SET @ID = '16'
;with getDepList as
(
  select * from tbDepList where pid=@id
  union all
  select a.* from tbDepList a join getDepList b on a.pid=b.fid
)
 
select * from getDepList