SQL的递归查询多级菜单

with tmpTable
as
(
-- 1、根节点
select * from tableName where parentId = 'xxx'
union all
-- 2、递归条件
select a.* from tableName a inner join tmpTable b on a.parentId = b.id
)
select * from tmpTable;

 

参考：https://blog.csdn.net/shijie_nihao/article/details/100717147

 

上面是向下查询的，下面的是向上查询的，其实代码是一样的，只是把 a.id= b.parentId 换一下

with tmpTable
as
(
-- 1、根节点
select * from tableName where parentId = 'xxx'
union all
-- 2、递归条件
select a.* from tableName a inner join tmpTable b on a.id= b.parentId 
)
select * from tmpTable;