Sqlserver递归查询所有上级或所有下级成员

--菜单目录结构表
create table tb_menu(
 id int not null, --主键id
 title varchar(50), --标题
 parent int --parent id
);
 
--查找所有上级节点
with cte_parent(id,title,parent)
as
(
    select id,title,parent    from tb_menu    where id = 1   --列出子节点查询条件
    union all
    select a.id,a.title,a.parent    from tb_menu a    inner join     cte_parent b          --执行递归，这里就要理解下了
    on a.id=b.parent   
)                        
select * from cte_parent;
 
----------------------------------------------
 
 
--查找下级节点带level
with cte_child(id,title,parent,level)
as
(
    select id,title,parent,0 as level from tb_menu    where id = 1--列出父节点查询条件
    union all
    select a.id,a.title,a.parent,b.level+1 from tb_menu a
    inner join  cte_child b on ( a.parent=b.id)   
)
 select  * from cte_child;