一对多查询跟显示全路径Sql-技术开发专区

一对多查询跟显示全路径Sql

作者：ITPUB论坛编辑： nancy 2008-06-14 00:00

【IT168技术文档】

表Document跟DocumentsFolderItem是一对多的关系，即一个Document文件可以放多个Document文件夹，那现在我想实现就是查询出Document记录，但document不重复，sql的写法如下：

SELECT TOP 100 PERCENT dbo.Document.*
FROM dbo.Document LEFT OUTER JOIN
dbo.DocumentsFolderItem ON
dbo.Document.DocID = dbo.DocumentsFolderItem.DocID AND (FolderID is null or
FolderID IN
(SELECT MAX(FolderID)
FROM dbo.DocumentsFolderItem where DocID=Document.DocID
GROUP BY docid))

我还要实现一个功能是，显示Folder的全路径，
格式如下：ParentFolder/ChildFolder
创建了一个定义发函数，实现根据FolderID返回所有上级路径的文件夹名称

CREATE function GetDocumentFolderPath(@FolderID bigint)
returns nvarchar(1000)
as
begin
declare @s nvarchar(1000),@Name nvarchar(20)
lab:
set @Name =(select Name from DocumentFolder where Status <> -1 and  FolderID=@FolderID)
select @FolderID=ParentID from DocumentFolder where Status <> -1 and  FolderID=@FolderID
if @Name is not null
begin
set @s=@Name+isnull('/'+@s,'')
goto lab
end
return @s
end
调用如下：
SELECT dbo.GetDocumentFolderPath(dbo.DocumentsFolderItem.FolderID) as Name from DocumentsFolderItem

关注我们