树形高手请进！！急！！

[spring123]

NodeID Text ParentID

1 物流公司 0
2 销售部 1
3 片区1 2
4 片区2 2
5 片区3 2
6 片区4 2
7 片区5 2
8 片区5-1 7
9 片区5-2 7
10 市场部 1
11 财务部 1
12 科技公司 0
13 软件部 10
14 测试部 10
15 测试1部 12

比如查询NodeID=8的“片区5-1”
数据，得到“物流公司，销售部，片区5，片区5－1”
这样的sql语句怎么写？

[子陌红尘]

create table BOM(NodeID int,Text varchar(20),ParentID int)
insert into BOM select 1 ,'物流公司',0
insert into BOM select 2 ,'销售部 ',1
insert into BOM select 3 ,'片区1 ',2
insert into BOM select 4 ,'片区2 ',2
insert into BOM select 5 ,'片区3 ',2
insert into BOM select 6 ,'片区4 ',2
insert into BOM select 7 ,'片区5 ',2
insert into BOM select 8 ,'片区5-1 ',7
insert into BOM select 9 ,'片区5-2 ',7
insert into BOM select 10,'市场部 ',1
insert into BOM select 11,'财务部 ',1
insert into BOM select 12,'科技公司',0
insert into BOM select 13,'软件部 ',10
insert into BOM select 14,'测试部 ',10
insert into BOM select 15,'测试1部 ',12
go

create function f_str(@NodeID int)
returns varchar(8000)
as
begin
declare @ret varchar(8000),@ParentID int
select @ret=Text,@ParentID=ParentID from BOM where NodeID=@NodeID
while @@rowcount<>0
begin
set @NodeID=@ParentID
select @ret=Text+','+@ret,@ParentID=ParentID from BOM where NodeID=@NodeID
end
return @ret
end
go

select dbo.f_str(NodeID) from BOM where Text='片区5-1'
go

/*
物流公司,销售部 ,片区5 ,片区5-1
*/

drop function f_str
drop table BOM

[bugchen888]

一条语句不行

DECLARE @result nvarchar(4000)
DECLARE @NodeID int
SET @result=''
SET @NodeID=8
SELECT @result=Text + ',' FROM [TABLE] WHERE NodeID=@NodeID
WHILE @@rowcount>0
BEGIN
SELECT @NodeID=ParentID FROM [TABLE] WHERE NodeID=@NodeID
SELECT @result=@result + Text + ',' FROM [TABLE] WHERE NodeID=@NodeID
END
SELECT @result

[xq02]

create table tb(NodeID int,Text varchar(20),ParentID int)
insert into tb select 1 ,'物流公司',0
insert into tb select 2 ,'销售部 ',1
insert into tb select 3 ,'片区1 ',2
insert into tb select 4 ,'片区2 ',2
insert into tb select 5 ,'片区3 ',2
insert into tb select 6 ,'片区4 ',2
insert into tb select 7 ,'片区5 ',2
insert into tb select 8 ,'片区5-1 ',7
insert into tb select 9 ,'片区5-2 ',7
insert into tb select 10,'市场部 ',1
insert into tb select 11,'财务部 ',1
insert into tb select 12,'科技公司',0
insert into tb select 13,'软件部 ',10
insert into tb select 14,'测试部 ',10
insert into tb select 15,'测试1部 ',12


create function f_str(@n_id int)
returns @re table(ParentID int,Text varchar(20),level int)
as
begin
declare @l int
set @l=0
insert @re select parentid,text, @l from tb
where nodeid=@n_id
while @@rowcount>0
begin
set @l=@l+1
insert @re select a.parentid,a.text,@l
from tb a,@re b
where a.nodeid=b.parentid and b.level=@l-1
end
return
end

select text from dbo.f_str(8)

text
--------------------
片区5-1
片区5
销售部
物流公司

（所影响的行数为 4 行）

[lcooc]

强,学习中^

[artoksxb]

Create function Ft_Bom(@nodeID int)
returns @tb1 table(NodeID int,Text1 varchar(20),ParentID int)
AS
begin
insert into @tb1 select NodeID,Text,ParentID from bom where nodeID=@nodeID
while @@rowcount>0
insert into @tb
select NodeID,Text,ParentID
from bom
where NodeID in(select ParentID from @tb1)and ParentID not in(select ParentID from @tb1)
return
end
--//
select * from ft_bom(8)

[mm2love2zz]

select [text] from [bom] where [nodeid] in (
select [parentid] from [bom] where [nodeid] in(
select [parentid] from [bom] where [nodeid] in(
select [parentid] from [bom] where [nodeid]='8')))
union all
select [text] from [bom] where [nodeid] in(
select [parentid] from [bom] where [nodeid] in(
select [parentid] from [bom] where [nodeid]='8'))
union all
select [text] from [bom] where [nodeid] in(
select [parentid] from [bom] where [nodeid]='8')
union all
select [text] from [bom] where [nodeid]='8'

[mm2love2zz]

上面的语句只是针对楼主提出的这个问题写的，实际使用起来也许不如用函数灵活~

libin_ftsafe(子陌红尘)和楼上几位朋友的函数都写的很好呀！~