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 ;

查詢結果：

SqlServer的遞迴查詢 也就是 with cte查詢