(转)cte 查询所有上级

create table tb(id int,fid int,name nvarchar(10))
insert into tb select 1,0,'百度知道'
insert into tb select 2,1,'电脑/网络'
insert into tb select 3,2,'程序设计'
insert into tb select 4,3,'数据库程序设计'
insert into tb select 5,1,'电脑/单机'
go
declare @current nvarchar(10)
set @current='程序设计'
;with cte as(
select * from tb where name=@current
union all
select a.* from tb a inner join cte b on a.id=b.fid
),c1 as(
select top 100 name from cte order by fid
)
select stuff((select '_'+[name] from c1 for xml path('')),1,1,'')
go
drop table tb

 

简单ID查询方式

declare @current int
set @current=3

;with cte as(
select * from tb where id=@current
union all
select a.* from tb a inner join cte b on a.id=b.fid
)
select id, name from cte order by fid