sql server 2014 树形结构数据查询 范例

-- with 的用法与oracle等语法类似  表中需有id和父id字段，查询结果将路径格式化输出
with tmp_tab(id,pid,code,path_,url,icon,curlevel)
as
(
--1，初始查询
select   id, pid, code,CAST( name as nvarchar(max)) path_,url,icon ,1 as level 
     from 
           dbo.T_SYS_RESOURCE 
     where 
           pid = '0AD044D4A190407EBE6173FB77C8EA97'
union all
--2,递归条件
select   a.id,  a.pid, a.code,
         --convert(varchar(100),convert(varchar(100),replicate('    ',b.curlevel+1) + a.name )) as name ,
		 CAST(b.path_+'//'+a.name as nvarchar(max)) as path_,
		 a.url,a.icon,
         b.curlevel+1                          
    from 
         dbo.T_SYS_RESOURCE  a 
    inner join 
         tmp_tab  b
    on (a.pid = b.id)
)
select * from tmp_tab;