sql 查询子级和父级

递归方式

--子级
with cte as
(
select 0 as LVL,* from Customer  with (nolock) where CId =636
union all
select LVL+1 ,c.* from Customer c  with (nolock)  join cte p
on c.ParentId = p.CId
)
select  *  from cte  where LVL > 0

--父级
with cte as
(
select 0 as LVL,* from Customer  with (nolock) where CId = 632
union all
select LVL+1 , p.* from cte c  join Customer p  with (nolock)
on c.ParentId = p.CId
)
select  * from cte c where LVL > 0 and LVL<4      //LVL级别