sqlserver 递归查询

--递归查询所有上级用户

with my1 as

(

select *,0 as lvl from [OneBuy].[dbo].[T_User] where UserId = 4

  union all

   select [OneBuy].[dbo].[T_User].* ,lvl+1  

   from my1, [OneBuy].[dbo].[T_User]    

where my1.ParentId = [OneBuy].[dbo].[T_User].UserId  

)

select * from my1 ;  

 --递归查询所有下级用户

 with my1 as

 (  

select *,0 as lvl from [OneBuy].[dbo].[T_User] where UserId = 3

  union all

  select [OneBuy].[dbo].[T_User].* ,lvl+1

from my1, [OneBuy].[dbo].[T_User]  

where my1.UserId = [OneBuy].[dbo].[T_User].ParentId

 )

 select * from my1 where lvl>0 and lvl <4;