cs_Folder_MoveFolder ALTERPROC[dbo].cs_Folder_MoveFolder @FolderIdINT, @NewParentFolderIdINT=NULL AS /**//*更新那个ParentFolderId*/ UPDATE cs_Folder SET ParentFolderId =@NewParentFolderId WHERE FolderId =@FolderId
cs_Folder_GetSummary ALTERPROC[dbo].cs_Folder_GetSummary @UserIdINT, @SettingsIDINT, @FolderIdINT=NULL AS BEGIN IF (@FolderIdISNULL) BEGIN SET@FolderId=-1 END -- Give me the top 10 most active blog posts --获取前10条最活跃的博客(应该是显示显示在主页上的吧) SELECTTOP10 fp.FeedPostId, fp.FeedId, fp.Author, fp.Title, fp.Description, fp.Source, fp.GuidName, fp.GuidIsPermaLink, fp.Link, fp.PubDate, fp.CommentsUrl, fp.EnclosureUrl, fp.EnclosureLength, fp.EnclosureType, fp.Creator, fp.CommentApiUrl, fp.CommentRssUrl, fp.CommentCount FROM cs_Feed f, cs_FeedPost fp, cs_FolderFeed ff WHERE f.FeedId = fp.FeedId AND fp.PubDate >=DATEADD(day, -15, GetDate()) AND f.FeedId = ff.FeedId AND ff.UserId =@UserId ANDIsNull(ff.FolderId,-1) =@FolderId AND f.SettingsID =@SettingsIDAND ff.SettingsID =@SettingsID ORDERBY fp.CommentCount DESC SELECTtop100 fp.FeedPostId, fp.FeedId, fp.Author, fp.Title, fp.Description, fp.Source, fp.GuidName, fp.GuidIsPermaLink, fp.Link, fp.PubDate, fp.CommentsUrl, fp.EnclosureUrl, fp.EnclosureLength, fp.EnclosureType, fp.Creator, fp.CommentApiUrl, fp.CommentRssUrl, fp.CommentCount FROM cs_Feed f, cs_FeedPost fp, cs_FolderFeed ff WHERE f.FeedId = fp.FeedId AND f.FeedId = ff.FeedId AND ff.UserId =@UserId ANDIsNull(ff.FolderId,-1) =@FolderId AND f.SettingsID =@SettingsIDAND ff.SettingsID =@SettingsID ORDERBY fp.FeedPostId DESC END
cs_Folder_GetFolders ALTERPROC[dbo].cs_Folder_GetFolders @UserIdINT, @SettingsIDINT, @ParentFolderIdINT=-1 AS Begin /**//*创建临时表,然后删除,语句有点负责的样子,不过认真看就没问题了。*/ CreateTable #Temp ( FeedId int, FolderId int, CNT int ) Insert #Temp (FeedId, FolderId, CNT) Select FeedId, cf.FolderId, ( selectcount(*) from (Selecttop50 f.FeedPostID, f.FeedId FROM cs_FeedPost f where f.FeedId = cf.FeedId orderby PubDate DESC) fp leftouterjoin cs_UserReadPost urp on fp.FeedPostID = urp.FeedPostID where fp.FeedId = cf.FeedId and urp.UserId ISNULL ) as[CNT]FROM cs_FolderFeed cf, cs_Folder ff where cf.FolderId = ff.FolderId andIsNull(ff.ParentFolderId, -1) =@ParentFolderIdand cf.UserId =@UserIdand cf.SettingsID =@SettingsIDand ff.SettingsID =@SettingsID SELECT FolderId, UserId, FolderName, ParentFolderId ,[UnreadCount]= (SelectSum(CNT) FROM #Tempwhere #Temp.FolderId = cs_Folder.FolderID) FROM cs_Folder WHERE UserId =@UserId ANDIsNull(ParentFolderId,-1) =@ParentFolderId AND cs_Folder.SettingsID =@SettingsID ORDERBY FolderName DROPTable #Temp END