我有一张桌子Folders
包含有关文件夹的分层信息:
FolderID FolderName ParentID
1 Folder1 0
2 Folder2 1
3 Folder3 2
4 Folder4 3
For Folder4
我想获取以下格式的父文件夹:
Folder1\Folder2\Folder3\
Note:我问过这个before,但我无法使用 CTE,因为我使用的是 SQL-Server 2000。
我已经编写了一个 SQL 函数,它应该返回您要查找的内容。
/* Set up test data */
create table Folders (
FolderID int,
FolderName varchar(10),
ParentID int
)
insert into Folders
(FolderID, FolderName, ParentID)
select 1,'Folder1',0 union all
select 2,'Folder2',1 union all
select 3,'Folder3',2 union all
select 4,'Folder4',3
go
/* Create function */
create function dbo.CreateFolderPath (@FolderID int)
returns varchar(1000)
as
begin
declare @ParentID int
declare @FolderPath varchar(1000)
set @FolderPath = ''
select @ParentID = ParentID
from Folders
where FolderID = @FolderID
while @ParentID<>0 begin
select @FolderPath = FolderName + '\' + @FolderPath, @ParentID = ParentID
from Folders
where FolderID = @ParentID
end /* while */
return @FolderPath
end /* function */
go
/* Demo the function */
select dbo.CreateFolderPath(4)
/* Clean up after demo */
drop function dbo.CreateFolderPath
drop table Folders
本文内容由网友自发贡献,版权归原作者所有,本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容,请联系:hwhale#tublm.com(使用前将#替换为@)