DNT的几个分页存储过程解析
在DNT安装时会创建一些存储过程，用于完成DNT的功能。
这些存储过程位于、upload_files\install\sqlscript\sqlserver文件夹中的setup2.2 - 2005.sql文件中。
其中的分页存储过程如下：
我认识存储过程这个东西不久，仔细看了下，注释出来.一直以为存储过程很高深,多看代码感觉也不过如此.就是一些函数而已
下边列举的4个分页存储过程,写法风格各不相同.
在算法上只有两种.一个使用了ROW_NUMBER() OVER ( ORDER BY [aid] DESC ) AS ROWID这个函数.另一个手动计算页码,将该页的所有数据以DESC的方式选到一个临时表中.再在这个表中搜索Top数据.然后返回.
从风格上讲,前两个使用了很多参数来组织语句,代码美观大方.后两个先组织了Sql字符串,加入搜索条件后才使用EXEC语句执行.代码乱七八糟的,不是很好看,但也实现了功能.
(1) dnt_getfavoriteslist 获取用户收藏的存储过程
这个存储过程用于查看用户收藏是的分页显示.返回一个包含帖子主题的表供前台调用
CREATE PROCEDURE [dnt_getfavoriteslist] --分页获取收藏主题表
@uid INT , --用户ID
@pagesize INT , --页大小
@pageindex INT --第几页
AS --SELECT [f].[uid],[f].[tid],[topics].[title],[topics].[poster],[topics].[postdatetime],[topics].[replies],[topics].[views],[topics].[posterid] FROM [dnt_favorites] [f] LEFT JOIN [dnt_topics] [topics] ON [f].[tid]=[topics].[tid] WHERE [f].[typeid]=0 AND [f].[uid]=@uid
IF @pageindex = 1 --处理第一页
BEGIN
SELECT TOP ( @pagesize ) --因为是第一页，选择出前@pagesize就是所需要的内容
[uid] ,
[tid] ,
[title] ,
[poster] ,
[postdatetime] ,
[replies] ,
[views] ,
[posterid]
FROM ( SELECT [f].[uid] , --这个Select用于选出用户用户所有的收藏 [f]代表表[dnt_favorites]，[topics]代表[dnt_topics]
--两个表连接起来查询出用户收藏的所有主题
[f].[tid] ,
[topics].[title] ,
[topics].[poster] ,
[topics].[postdatetime] ,
[topics].[replies] ,
[topics].[views] ,
[topics].[posterid]
FROM [dnt_favorites] [f] --此处设置f代表[dnt_favorites]
LEFT JOIN [dnt_topics] [topics] ON [f].[tid] = [topics].[tid]
WHERE [f].[typeid] = 0
AND [f].[uid] = @uid
) favorites --此处的favorites相当于一个表。From语句后边的SELECT语句选择出来的内容存放着这个表中
ORDER BY [tid] DESC
END
ELSE
BEGIN --@pageindex不等于1，处理其他页
SELECT TOP ( @pagesize ) --不是第一页，需要对选择出来内容的范围加以约束。Where之后未约束
[uid] ,
[tid] ,
[title] ,
[poster] ,
[postdatetime] ,
[replies] ,
[views] ,
[posterid]
FROM ( SELECT [f].[uid] ,
[f].[tid] ,
[topics].[title] ,
[topics].[poster] ,
[topics].[postdatetime] ,
[topics].[replies] ,
[topics].[views] ,
[topics].[posterid]
FROM [dnt_favorites] [f]
LEFT JOIN [dnt_topics] [topics] ON [f].[tid] = [topics].[tid]
WHERE [f].[typeid] = 0
AND [f].[uid] = @uid
) f1
WHERE [tid] < ( SELECT MIN([tid]) --根据页大小和页序号设置TID的范围
FROM ( SELECT TOP ( ( @pageindex - 1 )
* @pagesize )
[tid]
FROM ( SELECT [f].[uid] , --这里跟处理第一页一样，选择出所有收藏的内容作为一个表，供查询
[f].[tid] ,
[topics].[title] ,
[topics].[poster] ,
[topics].[postdatetime] ,
[topics].[replies] ,
[topics].[views] ,
[topics].[posterid]
FROM [dnt_favorites] [f]
LEFT JOIN [dnt_topics] [topics] ON [f].[tid] = [topics].[tid]
WHERE [f].[typeid] = 0
AND [f].[uid] = @uid
) f2
ORDER BY [tid] DESC --因为是DESC所以最上边的内容为需要找的对象
) AS tblTmp
)
ORDER BY [tid] DESC
END
GO
(2) dnt_getmyattachments 获取用户附件列表存储过程
CREATE PROCEDURE [dnt_getmyattachments] --获取用户附件并分页
@uid INT ,
@pageindex INT , --输入页码
@pagesize INT --输入每一页的大小
AS
DECLARE @startRow INT ,
@endRow INT
SET @startRow = ( @pageindex - 1 ) * @pagesize + 1 --计算查找数据的开始和结束行号
SET @endRow = @startRow + @pagesize - 1
SELECT [ATTACHMENTS].[aid] ,
[ATTACHMENTS].[uid] ,
[ATTACHMENTS].[attachment] ,
[ATTACHMENTS].[description] ,
[ATTACHMENTS].[downloads] ,
[ATTACHMENTS].[extname] ,
[ATTACHMENTS].[filename] ,
[ATTACHMENTS].[pid] ,
[ATTACHMENTS].[postdatetime] ,
[ATTACHMENTS].[tid]
FROM ( SELECT ROW_NUMBER() OVER ( ORDER BY [aid] DESC ) AS ROWID , --这里的Select相当于一个表(后边加上as)
--相当于 select * from ATTACHMENTS where
--ATTACHMENTS是SELECT ROW_NUMBER() OVER...的结果
--ROW_NUMBER()是一个函数，在这里的作用是给选择出来的结果加
--一个从1开始的连续增长的行号
--因为对于一个用户来讲，附件的aid是不连续的。
[dnt_myattachments].[aid] ,
[dnt_myattachments].[uid] ,
[dnt_myattachments].[attachment] ,
[dnt_myattachments].[description] ,
[dnt_myattachments].[downloads] ,
[dnt_myattachments].[extname] ,
[dnt_myattachments].[filename] ,
[dnt_myattachments].[pid] ,
[dnt_myattachments].[postdatetime] ,
[dnt_myattachments].[tid]
FROM [dnt_myattachments]
WHERE [dnt_myattachments].[uid] = @uid
) AS ATTACHMENTS
WHERE ROWID BETWEEN @startRow AND @endRow --根据ATTACHMENTS起止行序号选择数据输出
GO
(3) dnt_getfavoriteslistbyalbum 获取用户收藏列表并分页
CREATE PROCEDURE [dnt_getfavoriteslistbyalbum]
@uid INT ,
@pagesize INT ,
@pageindex INT
AS
DECLARE @strSQL VARCHAR(5000)
--下边这个语句找出所有用户收藏，临时记录在@strSQL中
SET @strSQL = 'SELECT [f].[tid], [f].[uid], [albumid], [albumcateid], [userid] AS [posterid], [username] AS [poster], [title], [description], [logo], [password], [imgcount], [views], [type], [createdatetime] AS [postdatetime] FROM [dnt_favorites] [f],[dnt_albums] [albums] WHERE [f].[tid]=[albums].[albumid] AND [f].[typeid]=1 AND [f].[uid]='
+ STR(@uid)
IF @pageindex = 1
BEGIN --第一页的处理，简单的SELECT TOP
SET @strSQL = 'SELECT TOP ' + STR(@pagesize)
+ ' [tid], [uid], [albumid], [albumcateid], [posterid], [poster], [title], [description], [logo], [password], [imgcount], [views], [type], [postdatetime] FROM ('
+ @strSQL + ') f' + ' ORDER BY [tid] DESC'
END
ELSE
BEGIN --其他页的处理，跟第1种的处理相似 也是选出( @pageindex - 1 ) * @pagesize个数据，逆序排列后返回前@pagesize个数据
SET @strSQL = 'SELECT TOP ' + STR(@pagesize)
+ ' [tid], [uid], [albumid], [albumcateid], [posterid], [poster], [title], [description], [logo], [password], [imgcount], [views], [type], [postdatetime] FROM ('
+ @strSQL
+ ') f1 WHERE [tid] < (SELECT MIN([tid]) FROM (SELECT TOP '
+ STR(( @pageindex - 1 ) * @pagesize) + ' [tid] FROM ('
+ @strSQL + ') f2' + ' ORDER BY [tid] DESC) AS tblTmp)'
+ ' ORDER BY [tid] DESC'
END
EXEC(@strSQL) --组织完SQL语句，执行以下就OK了
GO BEGIN
SET @strSQL = 'SELECT TOP ' + STR(@pagesize)
+ ' [tid], [uid], [albumid], [albumcateid], [posterid], [poster], [title], [description], [logo], [password], [imgcount], [views], [type], [postdatetime] FROM ('
+ @strSQL
+ ') f1 WHERE [tid] < (SELECT MIN([tid]) FROM (SELECT TOP '
+ STR(( @pageindex - 1 ) * @pagesize) + ' [tid] FROM ('
+ @strSQL + ') f2' + ' ORDER BY [tid] DESC) AS tblTmp)'
+ ' ORDER BY [tid] DESC'
END
EXEC(@strSQL)
GO
(4) dnt_getattentiontopics 分页获取需要被关注的主题列表
CREATE PROCEDURE [dnt_getattentiontopics] --分页获取需要被关注的主题列表
@fid VARCHAR(255) = '' ,
@tpp INT , --每页显示数量
@pageid INT , --页码
@condition NVARCHAR(255) = '' --搜索条件
AS
DECLARE @pagetop INT ,
@strSQL VARCHAR(5000)
SET @pagetop = ( @pageid - 1 ) * @tpp
IF @pageid = 1
BEGIN
SET @strSQL = 'SELECT TOP ' + STR(@tpp)
+ ' * FROM [dnt_topics] WHERE [displayorder]>=0 AND [attention]=1'
IF @fid <> '0'
SELECT @strSQL = @strSQL + ' AND [fid] IN (' + @fid + ')'
IF @condition <> ''
SELECT @strSQL = @strSQL + @condition
SELECT @strSQL = @strSQL + ' ORDER BY [lastpost] DESC'
END
ELSE
BEGIN
SET @strSQL = 'SELECT TOP ' + STR(@tpp)
+ ' * FROM [dnt_topics] WHERE [tid] < (SELECT MIN([tid]) FROM (SELECT TOP ' --也是选择出来顶上的( 页码 - 1 ) * 每页数量个行
+ STR(@pagetop)
+ ' [tid] FROM [dnt_topics] WHERE [displayorder]>=0 AND [attention]=1'
IF @fid <> '0' --加入搜索条件
SELECT @strSQL = @strSQL + ' AND [fid] IN (' + @fid + ')'
IF @condition <> ''
SELECT @strSQL = @strSQL + @condition
SELECT @strSQL = @strSQL + ' ORDER BY [tid] DESC'
SELECT @strSQL = @strSQL + ' ) AS T) '
IF @fid <> '0'
SELECT @strSQL = @strSQL + ' AND [fid] IN (' + @fid + ')'
IF @condition <> ''
SELECT @strSQL = @strSQL + @condition
SELECT @strSQL = @strSQL
+ ' AND [displayorder]>=0 AND [attention]=1 ORDER BY [tid] DESC'
END
EXEC(@strSQL)
GO
顶部那段话本来想放到这里的,可想想谁会看这么长的代码啊,Ctrl+C Ctrl+V了一下
