SqlServer的递归查询 也就是 with cte查询

表结构 

表数据

 

SQL如下

with lmenu 
as
(
 select name,id,1 as lv,cast(Name as nvarchar(max)) as name1, cast(ROW_NUMBER()over(order by getdate()) as nvarchar(10)) as orderid   from Table2 where Table2.ParentId=0 
  Union all
  select a.Name ,a.ID,(b.lv+1) as lv,cast( REPLICATE('  ',b.lv*2)+ a.name as nvarchar(max)) as name1,
  cast( ( b.orderid+'.'+ cast(ROW_NUMBER()over(Order by GETDATE() ) as nvarchar(5))) as nvarchar(10)) as   orderid 
  from Table2 as a inner join lmenu as b on a.ParentId=b.id
)
select * from lmenu  order by   orderid ;

查询结果：