请问如何获得树型记录的全路径?

[lvloj]

表结构
ID, PID, NAME, PATH
1 0 A /
2 1 B /1/
3 2 C /1/2/
4 0 D /
5 4 E /4/
6 2 F /1/2/
7 3 G /1/2/3/
需要经过查询获得[FULL_NAME],请问如何写SQL,数据库SQLServer2000.
ID, FULL_NAME
1 A
2 A/B
3 A/B/C
4 D
5 D/E
6 A/B/F
7 A/B/C/G

[liangCK]

--测试数据
DECLARE @t TABLE(ID char(3),PID char(3),Name nvarchar(10))
INSERT @t SELECT '001',NULL ,'山东省'
UNION ALL SELECT '002','001','烟台市'
UNION ALL SELECT '004','002','招远市'
UNION ALL SELECT '003','001','青岛市'
UNION ALL SELECT '005',NULL ,'四会市'
UNION ALL SELECT '006','005','清远市'
UNION ALL SELECT '007','006','小分市'

--深度排序显示处理
--生成每个节点的编码累计(相同当单编号法的编码)
DECLARE @t_Level TABLE(ID char(3),Level int,Sort varchar(8000))
DECLARE @Level int
SET @Level=0
INSERT @t_Level SELECT ID,@Level,ID
FROM @t
WHERE PID IS NULL
WHILE @@ROWCOUNT>0
BEGIN
	SET @Level=@Level+1
	INSERT @t_Level SELECT a.ID,@Level,b.Sort+a.ID
	FROM @t a,@t_Level b
	WHERE a.PID=b.ID
		AND b.Level=@Level-1
END

--显示结果
SELECT SPACE(b.Level*2)+'|--'+a.Name
FROM @t a,@t_Level b
WHERE a.ID=b.ID
ORDER BY b.Sort
/*--结果
|--山东省
  |--烟台市
    |--招远市
  |--青岛市
|--四会市
  |--清远市
    |--小分市
--*/

[wzy_love_sly]

...这个写完得时间了

[nzperfect]

--> 测试数据: [T]
if object_id('[T]') is not null drop table [T]
create table [T] (ID int,PID int,NAME varchar(1),PATH varchar(7))
insert into [T]
select 1,0,'A','/' union all
select 2,1,'B','/1/' union all
select 3,2,'C','/1/2/' union all
select 4,0,'D','/' union all
select 5,4,'E','/4/' union all
select 6,2,'F','/1/2/' union all
select 7,3,'G','/1/2/3/'


select *,dbo.fn_getcn(path,id) from [T]
/*
1	0	A	/	       /A
2	1	B	/1/	       /A/B
3	2	C	/1/2/	   /A/B/C
4	0	D	/	       /D
5	4	E	/4/	       /D/E
6	2	F	/1/2/	   /A/B/F
7	3	G	/1/2/3/	   /A/B/C/G
*/


--需要两个函数：
create function dbo.fn_getcn(@str varchar(2000),@id int)
returns varchar(2000)
as
begin
    declare @s varchar(8000)
    set @s=''
    select @s=@s+NAME+'/' from dbo.f_split(@str,'/') as a 
    inner join T as b on a.a=b.id
    select @s=@s+name from T where id=@id
    if len(@s)>0 set @s='/'+@s
    return @s
end

create function f_split(@str varchar(8000),@StrSeprate varchar(1))  
returns @temp table(a varchar(100))  
as   
begin  
    declare @i int  
    set @str=rtrim(ltrim(@str))  
    set @i=charindex(@StrSeprate,@str)  
    while @i>=1  
    begin  
        insert @temp values(left(@str,@i-1))  
        set @str=substring(@str,@i+1,len(@str)-@i)
        set @i=charindex(@StrSeprate,@str)  
    end  
    if @str<>'\'  
       insert @temp values(@str)  
    return   
end

[雲水 禅心]

zheyang

[wzy_love_sly]

create table tb(id int,pid int,name varchar(20),path varchar(50))
insert into tb select 1,0,'a','/'
insert into tb select 2,1,'b','/1/'
insert into tb select 3,2,'c','/1/2/'
insert into tb select 4,0,'d','/'
insert into tb select 5,4,'e','/4/'
insert into tb select 6,2,'f','/1/2/'
insert into tb select 7,3,'g','/1/2/3/'

create function repalce_s(@id varchar(50))
returns varchar(8000)
as
begin
declare @sql varchar(8000)
select @sql=isnull(@sql,'')+name+'/' from (
select a.id,b.name
from tb a,(select id,name from tb)b
where charindex('/'+ltrim(b.id)+'/',a.path)>0
) t where id=@id
return @sql
end

select id,isnull(dbo.repalce_s(id)+name,name) as path from tb

id path
1 a
2 a/b
3 a/b/c
4 d
5 d/e
6 a/b/f
7 a/b/c/g

[dobear_0922]

老问题，自己写个函数吧

[wzy_love_sly]

..半小时，结帖否？

[nzperfect]

[Quote=引用 7 楼 wzy_love_sly 的回复:]
..半小时，结帖否？
[/Quote]

如果每个人都能发贴结贴,你就不是两个星了

[LIHY70]

create function return_str(@idstr varchar(50))
returns varchar(8000)
as
begin
declare @sql varchar(8000)
select @sql=isnull(@sql,'')+name+'/' from tb where charindex('/'+ltrim(id)+'/',@idstr+'/')>0

return right(@sql,2)
end

select id,isnull(dbo.return_str(path+cast(id as varchar)),'') as path from tb

[pt1314917]

create table tb(id int,pid int,name varchar(20),path varchar(50))
insert into tb select 1,0,'a','/'
insert into tb select 2,1,'b','/1/'
insert into tb select 3,2,'c','/1/2/'
insert into tb select 4,0,'d','/'
insert into tb select 5,4,'e','/4/'
insert into tb select 6,2,'f','/1/2/'
insert into tb select 7,3,'g','/1/2/3/'
go

--创建存储过程
create proc wsp
as
update tb set path=name
DECLARE @i int,@j int
set @i=0
set @j=1
select @i=max(pid) from tb
while @j<=@i
begin
	update tb set  path=a.path+'\'+tb.name 
	from tb inner join tb a on tb.pid=a.id 
	where tb.pid=@j 
	set @j=@j+1
end
select * from tb
go

--调用存储过程
exec wsp