--菜单目录结构表
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;
