sqlserver 递归查询：向上向下

sqlserver 递归查询：向上向下

--由父项递归下级 
with cte(id,parentid,text) 
as 
(--父项 
select id,parentid,text from treeview where parentid = 450 
union all 
--递归结果集中的下级 
select t.id,t.parentid,t.text from treeview as t 
inner join cte as c on t.parentid = c.id 
) 
select id,parentid,text from cte 


--------------------- 


--由子级递归父项 
with cte(id,parentid,text) 
as 
(--下级父项 
select id,parentid,text from treeview where id = 450 
union all 
--递归结果集中的父项 
select t.id,t.parentid,t.text from treeview as t 
inner join cte as c on t.id = c.parentid 
) 

select id,parentid,text from cte

-----------------------------

eg：

写个带返回值的存储过程调用一下，比较快：

create proc getOrgId(
    @GroupID int,--输入参数
    @id int output --输出参数
)
as    
   with cte([GroupID],[G_ParentID],[G_Level])
as
(
select [GroupID],[G_ParentID],[G_Level] from [sys_Group] where [GroupID] =@GroupID
union all
select t.[GroupID],t.[G_ParentID],t.[G_Level] from [sys_Group] as t
inner join cte as c on t.[GroupID] = c.[G_ParentID]
)
select @id=[GroupID] from cte  where cte.[G_Level]='2'

--执行这个带返回值的存储过程
declare @orgid int --声明一个变量用来接收执行存储过程后的返回值
exec getOrgId '1734',@orgid output
select @orgid as orgid;--as是给返回的列值起一个名字