--递归查询所有上级用户
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;