关注我
将上一篇的最终查询结果的SQL的语句做成一个视图：
select ArticleId,Title,CreatedTime,ModifiedTime,LoginName Author,Clicks,CommentCount,Commentator,LastCommentTime
from
(select C.ArticleId,Title,CreatedTime,ModifiedTime,UserId,Clicks,CommentCount,Commentator,LastCommentTime
from
(select ArticleId,CommentCount=0,Commentator=NULL,LastCommentTime=NULL from Article where ArticleId not in(
select A.ArticleId from
(select ArticleId,COUNT(ArticleId) CommentCount,
MAX(CommentDate) LastCommentTime
from ArticleComment group by ArticleId) A
join ArticleComment on a.ArticleId=ArticleComment.ArticleId and LastCommentTime=CommentDate)
union
select ArticleId,CommentCount,LoginName Commentator,LastCommentTime
from
(select A.ArticleId,CommentCount,UserId,LastCommentTime from
(select ArticleId,COUNT(ArticleId) CommentCount,
MAX(CommentDate) LastCommentTime
from ArticleComment group by ArticleId) A
join ArticleComment on a.ArticleId=ArticleComment.ArticleId and LastCommentTime=CommentDate
) B
join UserInfo on(B.UserId=UserInfo.UserId)
) C
join Article on(c.ArticleId=Article.ArticleId)
) D
join UserInfo on(D.UserId=UserInfo.UserId)
即，视图的语法为：
create view View_Home_SelectArticles
as
select B.ArticleId,Title,AuthorId,LoginName Author,CreatedTime,ModifiedTime,Clicks,CommentCount,LastCommentTime,Commentator from
(
select CommentCountTable.ArticleId,Article.Title,Article.UserId AuthorId,Article.CreatedTime,Article.ModifiedTime, CommentCount,Article.Clicks,Commentator,LastCommentTime from
(select ArticleId, CommentCount,LoginName Commentator,LastCommentTime from
(select a.ArticleId,a.CommentCount,a.LastCommentTime,UserId CommentatorId from (select ArticleId,COUNT(ArticleId) CommentCount,MAX(CommentDate) LastCommentTime from ArticleComment group by ArticleId) A
join ArticleComment on a.ArticleId=ArticleComment.ArticleId and a.LastCommentTime=ArticleComment.CommentDate
)
as CommentTableA
join UserInfo on CommentTableA.CommentatorId=UserInfo.UserId
union
select ArticleId, CommentCount=0,Commentator=null,LastCommentTime=null from Article where ArticleId not in(select ArticleId from ArticleComment)
) as CommentCountTable
join Article on Article.ArticleId=CommentCountTable.ArticleId
) B join UserInfo on b.AuthorId=UserInfo.UserId
GO
查询视图的语法为：
select * from View_Home_SelectArticles
下面根据该视图构建存储过程：
CREATE PROCEDURE [dbo].[usp_GetArticlePagerData]
-- Add the parameters for the stored procedure here
@strOrder nvarchar(255)='', -- 排序的字段名，必填
@strOrderType nvarchar(10)='ASC', -- 排序的方式,默认ASC
@pageSize int = 10, -- 页尺寸,默认
@pageIndex int = 1 -- 页码,默认
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- -- Insert statements for procedure here
declare @strSQL nvarchar(4000)=''
if(@strOrder = 'LastCommentTime')
begin
set @strSQL='select top '+CONVERT(varchar(3),@pageSize)+' * from '
+'(select * from (select top 99.999999 percent * from View_Home_SelectArticles where CommentCount>0 order by LastCommentTime '+@strOrderType+') t1'
+' union all'
+' select * from (select top 99.999999 percent * from View_Home_SelectArticles where CommentCount is NULL order by CreatedTime '+@strOrderType+') t2'
+') LastCommentTimeTable where ArticleId not in ('
+'select top '+CONVERT(varchar(10),(@pageIndex-1)*@pageSize)+' ArticleId from '
+'(select * from (select top 99.999999 percent * from View_Home_SelectArticles where CommentCount>0 order by LastCommentTime '+@strOrderType+') t1'
+' union all'
+' select * from (select top 99.999999 percent * from View_Home_SelectArticles where CommentCount is NULL order by CreatedTime '+@strOrderType+') t2'
+') LastCommentTimeTable)'
end
else
begin
if(@strOrder = 'CommentCount')
set @strSQL='select * from (select ROW_NUMBER() OVER (ORDER BY CommentCount '+@strOrderType+',LastCommentTime '+@strOrderType+',CreatedTime '+@strOrderType+') AS RowNumber,* from View_Home_SelectArticles)'
+' CurrentPager where RowNumber between '+str((@pageIndex-1)*@pageSize+1)+' and '+str(@pageIndex*@pageSize)
else if(@strOrder = 'Clicks')
set @strSQL='select * from (select ROW_NUMBER() OVER (ORDER BY Clicks '+@strOrderType+',CreatedTime '+@strOrderType+') AS RowNumber,* from View_Home_SelectArticles)'
+' CurrentPager where RowNumber between '+str((@pageIndex-1)*@pageSize+1)+' and '+str(@pageIndex*@pageSize)
else
set @strSQL='select * from (select ROW_NUMBER() OVER (ORDER BY '+@strOrder+' '+@strOrderType+') AS RowNumber,* from View_Home_SelectArticles)'
+' CurrentPager where RowNumber between '+str((@pageIndex-1)*@pageSize+1)+' and '+str(@pageIndex*@pageSize)
end
exec sp_executesql @strSQL
END
GO
该存储过程解释如下：
select * from (select top 99.999999 percent ArticleId, CommentCount,Author,CreatedTime,Commentator,LastCommentTime from View_Home_SelectArticles where CommentCount>0 order by LastCommentTime desc) t1
union all
select * from (select top 99.999999 percent ArticleId, CommentCount,Author,CreatedTime,Commentator,LastCommentTime from View_Home_SelectArticles where CommentCount is NULL order by CreatedTime desc) t2
上面的SQL语句表示：按照“最近回复时间”进行降序排序查询，如果没有用户回复，再按照文章的创建时间进行降序查询。
再看下面的SQL语句，表示按照“评论数量”进行升降序排列查询，当评论数量相同时，再按照最近一次的评论时间进行升降序查询，如果没有评论的，再按照发布时间的进行升降序查询，
if(@strOrder = 'CommentCount')
set @strSQL='select * from (select ROW_NUMBER() OVER (ORDER BY CommentCount '+@strOrderType+',LastCommentTime '+@strOrderType+',CreatedTime '+@strOrderType+') AS RowNumber,* from View_Home_SelectArticles)' +' CurrentPager where RowNumber between '+str((@pageIndex-1)*@pageSize+1)+' and '+str(@pageIndex*@pageSize)
最后的下面的SQL语句，表示当按照“点击次数”进行升降序排列查询时，如果点击次数相同，则再按照文章的创建时间进行升降序查询，
if(@strOrder = 'Clicks')
set @strSQL='select * from (select ROW_NUMBER() OVER (ORDER BY Clicks '+@strOrderType+',CreatedTime '+@strOrderType+') AS RowNumber,* from View_Home_SelectArticles)' +' CurrentPager where RowNumber between '+str((@pageIndex-1)*@pageSize+1)+' and '+str(@pageIndex*@pageSize)