如何使用 SQL-Server 2000 查询层次信息? [英] How to query hierarchical information with SQL-Server 2000?
本文介绍了如何使用 SQL-Server 2000 查询层次信息?的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我有一个表 Folders
包含有关文件夹的层次结构信息:
I've got a table Folders
with hierarchical information about folders:
FolderID FolderName ParentID
1 Folder1 0
2 Folder2 1
3 Folder3 2
4 Folder4 3
对于 Folder4
我想以下列格式获取父文件夹:
For Folder4
I would like to get the parent folders in the following format:
Folder1\Folder2\Folder3\
<小时>
注意:我在之前问过这个问题,但我不能使用 CTE,因为我使用的是 SQL-Server 2000.
Note: I have asked this before, but I cannot use CTE because I am using SQL-Server 2000.
推荐答案
我已经编写了一个 SQL 函数,它应该返回您正在寻找的内容.
I've written a SQL function that should return what you're looking for.
/* 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
这篇关于如何使用 SQL-Server 2000 查询层次信息?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文