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
只是微笑地固执自己的坚持，
