一对多查询跟显示全路径Sql
表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