create table Tree(NodeId int,ParentId int,NodeName varchar(4))
insert into Tree select 0 ,-1,'一'
insert into Tree select 1 ,0 ,'二'
insert into Tree select 9 ,1 ,'三'
insert into Tree select 10,9 ,'四'
insert into Tree select 12,10,'五'
insert into Tree select 18,1 ,'六'
gocreate function f_getPath(@NodeId int)
returns varchar(8000)
as
begin
declare @ret varchar(8000),@ParentId int
set @ret = ''
select @ret=@ret+NodeName,@ParentId=ParentId from Tree where NodeId=@NodeId
while @@rowcount<>0
begin
set @NodeId=@ParentId
select @ret=@ret+NodeName,@ParentId=ParentId from Tree where NodeId=@NodeId
end
return @ret
end
go
select *,path=dbo.f_getPath(NodeId) from Tree
go/*
NodeId ParentId NodeName Path
0 -1 一 一
1 0 二 二一
9 1 三 三二一
10 9 四 四三二一
12 10 五 五四三二一
18 1 六 六二一
*/drop function f_getPath
drop table Tree
go
insert into Tree select 0 ,-1,'一'
insert into Tree select 1 ,0 ,'二'
insert into Tree select 9 ,1 ,'三'
insert into Tree select 10,9 ,'四'
insert into Tree select 12,10,'五'
insert into Tree select 18,1 ,'六'
gocreate function f_getPath(@NodeId int)
returns varchar(8000)
as
begin
declare @ret varchar(8000),@ParentId int
set @ret = ''
select @ret=@ret+NodeName,@ParentId=ParentId from Tree where NodeId=@NodeId
while @@rowcount<>0
begin
set @NodeId=@ParentId
select @ret=@ret+NodeName,@ParentId=ParentId from Tree where NodeId=@NodeId
end
return @ret
end
go
select *,path=dbo.f_getPath(NodeId) from Tree
go/*
NodeId ParentId NodeName Path
0 -1 一 一
1 0 二 二一
9 1 三 三二一
10 9 四 四三二一
12 10 五 五四三二一
18 1 六 六二一
*/drop function f_getPath
drop table Tree
go
说了一声 “强”