表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

posted @ 2008-06-01 00:03  花边软件,花边管理软件,服装(鞋)管理软件  Views(1023)  Comments()    收藏  举报