if OBJECT_ID('tempdb..tab') is not null drop table tab; go create table tab (id int, pid int, name varchar(10)); go insert into tab values (1,0,'Company'),(2,1,'Area01'),(3,1,'Area02'), (4,2,'Section01'),(5,3,'Section02'),(6,4,'Group01'), (7,4,'Group02'),(8,5,'Group03'),(9,5,'Group04'); go-- 输入 @id declare @id int=2;with t as( select id,pid,name,1 lvl, cast('<id lvl="1">'+name+'</id>' as varchar(max)) xpath from tab where id=@id union all select tab.id,tab.pid,tab.name,lvl+1, t.xpath+'<id lvl="'+ltrim(lvl+1)+'">'+tab.name+'</id>' from tab,t where t.id=tab.pid ), e as( select id,pid,name,cast(xpath as xml) xpath from t ) select e.id,p.pname,p.lvl into # from e cross apply ( select x.c.value('.','varchar(10)') pname, x.c.value('./@lvl[1]','int') lvl from e.xpath.nodes('/id') x(c)) p;declare @stmt varchar(2000),@pvt varchar(200); select @pvt=stuff((select distinct ','+quotename(ltrim(lvl)) from # for xml path('')),1,1,''); select @stmt='select '+@pvt+' from # pivot (max(pname) for lvl in ('+@pvt+')) pvt';exec(@stmt);drop table #; /* Area01 NULL NULL Area01 Section01 NULL Area01 Section01 Group01 Area01 Section01 Group02 */
id pid name
---- ---- ------
1 0 公司
2 1 区1
3 1 区2
4 2 处1
5 3 处2
6 4 组1
7 4 组2
8 5 组3
9 5 组4帖子发出来后排版就乱了,这是一张关系表,pid是父节点的id。 查询id = 2 也就是要查询区1的全部子节点,区1的全部子节点有处1,组1,组2 输出的是以下结果:上级2 上级1 团队
---- ---- ----
区1 空 空
区1 处1 空
区1 处1 组1
区1 处1 组2
if OBJECT_ID('tempdb..tab') is not null
drop table tab;
go
create table tab (id int, pid int, name varchar(10));
go
insert into tab values
(1,0,'Company'),(2,1,'Area01'),(3,1,'Area02'),
(4,2,'Section01'),(5,3,'Section02'),(6,4,'Group01'),
(7,4,'Group02'),(8,5,'Group03'),(9,5,'Group04');
go-- 输入 @id
declare @id int=2;with t as(
select id,pid,name,1 lvl,
cast('<id lvl="1">'+name+'</id>' as varchar(max)) xpath from tab where id=@id
union all
select tab.id,tab.pid,tab.name,lvl+1,
t.xpath+'<id lvl="'+ltrim(lvl+1)+'">'+tab.name+'</id>' from tab,t where t.id=tab.pid
),
e as(
select id,pid,name,cast(xpath as xml) xpath from t
)
select e.id,p.pname,p.lvl into #
from e cross apply (
select x.c.value('.','varchar(10)') pname,
x.c.value('./@lvl[1]','int') lvl
from e.xpath.nodes('/id') x(c)) p;declare @stmt varchar(2000),@pvt varchar(200);
select @pvt=stuff((select distinct ','+quotename(ltrim(lvl)) from # for xml path('')),1,1,'');
select @stmt='select '+@pvt+' from # pivot (max(pname) for lvl in ('+@pvt+')) pvt';exec(@stmt);drop table #;
/*
Area01 NULL NULL
Area01 Section01 NULL
Area01 Section01 Group01
Area01 Section01 Group02
*/