DNT分页存储过程解析

1、dnt的几个分页存储过程解析在dnt安装时会创建一些存储过程，用于完成dnt的功能。这些存储过程位于、upload_filesinstallsqlscriptsqlserver 文件夹中的 setup2. 2 - 2005. sql 文件中。其屮的分页存储过程如下：我认识存储过程这个东西不久，仔细看了下，注释出来.下边列举的4个分页存储过程,写法风格各不相同.在算法上只有两种.一个使用了 row_number () over ( order by aid desc ) as row id这个函数.另一个手动计算页码,将该页的所有数据以desc 的方式选到一个临时表中.再在这个表中搜索top数据

2、.然后返回.从风格上讲，前两个使用了很多参数来组织语句,代码美观大方.后两个先组织了 sql字符串，加入搜索条件后才使用exec语句执行.代码乱七八糟的，不是 很好看，但也实现了功能.(1) dnt_getfavoriteslist获取用户收藏的存储过程这个存储过程用于查看用户收藏是的分页显示返回一个包含帖子主题的表供前台调用create procedure dnt_getfavoriteslist-分页获取收藏主题表quid int ,-用户:edpagesize int ,-页大小qpageindex int 第几页as selectf.uid, f.tid, topics.titie,

3、topics.poster,topics.postdatetime,topics.replies, topics.views, topics.posterid from dnt_favorites f left join dnt_topics topics on f.tid=topics.tid wheref.typeid=0 and f.uid=uidif qpageindex = 1-处理第一页beginselect top ( pagesize )-因为是第一页，选择出前pagesize就是所需要的内容uid /tid,title /poster,postdatetime,replies

4、,views,posteridfrom ( select f . uid,-这个select用于选出用八用八所有的收藏f代表表dnt_f avorites » topics代表dnt_topics-两个表连接起来查询出用户收藏的所有主题f.tid,topics title /topicsposter,topicspostdatetime ztopicsreplies.topicsviews,topicsposteridfromdnt_favorites f 此处设置f代表dnt_favoritesleft join dnt_topics topics on ftid = topics

5、tidwheref.typeid = 0and fuid = uid)favorites -此处的favorites相当于一个表。from语句后边的select语句选择出來的内容存放着这个表中order by tid descendelsebegin -gpageindex不等于1,处理其他页select top ( qpagesize )-不是第一页，需要对选择出來内容的范圉加以约束。where之后未约束fromwhereuid /tid,title zposter,postdatetime areplies,views /posterid(selectf.uid,ftid,topics .

6、 title /topicsposter,topicsposztopicsreplies,topicsviews,topicsposteridfromdnt_favorites fleft join dnt_topics topics on f.tid = topicstidwhere ftypeid = 0and f.uid = uid)fltid < ( select min( tid)-根据页大小和页序号设置tid的范围from( select top ( pageindex - 1 )* qpagesize )tid查询from( select f . uid,-这里跟处理第一页

7、一样，选择出所有收藏的内容作为一个表，供f.tid,topics titie,topicsposter,topicspostdatetime,topicsreplies ftopicsviews,topicsposteridfrom dnt_favorites fleft join dnt_topics topics on f tid = topics tidwhere f typeid = 0and fuid = uid)f2order by tid desc-因为是desc所以最上边的内容为需要找的对象)as tbitmp)order by tid descendgo(2) dnt get

8、myattachments获取用户附件列表存储过程create procedure dnt_getmyattachments -获取用户附件并分页quid int ,pageindex int z-输入页码pagesize int-输入每一页的大小asdeclare ostartrow int ,qendrow intset startrow = ( gpageindex - 1 ) * gpagesize + 1set gendrow = qstartrow + qpagesize - 1select attachments.aid,attachments.uid,attachments.a

9、ttachment,attachmentsdescription,attachmentsdownloads,attachments.extname,attachments.filename,attachments.pid,attachmentspostdatetime,attachmentstidfrom ( select row_number () over ( order by aid果果加dnt_myattachmentsaid / dnt_myattachmentsuid, dnt_myattechmentstachment z dnt_myattachmentsdescription

10、, dnt_myattachments.downloads, dnt_myattachments.extname,-计算查找数据的开始和结束行号desc ) as row id ,-这里的select相当于一个表(后边加上as )-相当于 select * from attachments where-attachments是selectrow_number () over 的结row_number ()是一个函数，在这里的作用是给选择出来的结-一个从1开始的连续增长的行号-因为对于一个用户来讲，附件的aid是不连续的。dnt_myattachments.filename,dnt_myatta

11、chmentspid,dnt_myattachmentspostdatetime zdnt_myattachmentstidfromdnt_myattachmentswherednt_myattachments.uid = uid)as attachmentswhere row id between gstartrow and qendrow根据 attachments 起止行序号选择数据输岀go(3) dnt_get favorites listbyalbum 获取用户收藏列表并分页create procedure dnt_getfavoriteslistbyalbumquid int ,q

12、pagesize int ,pageindex intasdeclare strsql varchar(5000)-下边这个语句找出所有用户收藏，临时记录在strsql中set qstrsql = 1 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_favori

13、tes fz dnt_albums albums where ftid = albumsalbumid and ftypeid=1 and fuid = *+ str(uid)if qpageindex = 1begin-第一页的处理，简单的select topset sstrsql = ，select top 1 + str(pagesize)+ 1 tid, uid, albumidz albumcateid, posterid, poster, titie, description? logo, password/imgcount, views/ type, postdatetime f

14、rom ( 1+ qstrsql + *) f* + * order by tid desc'endelsebegin-其他页的处理，跟第1种的处理相似也是选出(qpageindex - 1 ) * pagesize个数据，逆序排列后返回前pagesize个数据set gstrsql = 'select top * + str(pagesize)+ 1 tidz uid, albumid, albumcateid, posterid, poster, titlez description, logo, passwordzimgcount, views, type, postda

15、tetime from (1+ strsql+ 1) f1 where tid < (select min(tid) from (select top 1+ str( ( qpageindex - 1 ) * gpagesize) + 1 tid from ('+ qstrsql + *) f2* + * order by tid desc) as tbltmp) 1+ ' order by tid desc'endexec (gstrsql) 组织完sql语句，执行以下就ok了gobeginset gstrsql = 'select top * + st

16、r(spagesize)+ 1 tid, uid, albumid, albumcateidz posterid, poster, title, description, logoz passwordzimgcount, views, type, postdatetime from (1+ qstrsql+ 1) fl where tid < (select min(tid) from (select top 1+ str ( ( qpageindex - 1 ) * qpagesize) + 1 tid from (1+ strsql + *) f2* + * order by tid

17、 desc) as tbitmp) 1+ 1 order by tid desc1endexec (gstrsql)go(4) dnt_get at tent iontopics分页获取需要被关注的主题列表create procedure dnt_getattentiontopics-分页获取需耍被关注的主题列表fid varchar(255) = 1 1 rtpp int ,-每页显示数量pageid int ,页码qcondition nvarchar(2 55) = * *-搜索条件asdeclare qpagetop int ,gstrsql varchar(5000)set page

18、top = ( qpageid - 1 ) * tppif opageid = 1beginset gstrsql = 'select top ' + str(tpp)+ 1* from dnt_topics where displayorder>=0 and attention=1if fid <> * 0 *select sstrsql = rsql + ' and fid in ( ' + fid + ')'if condition <> 11select qstrsql = qstrsql + conditionselect strsql = sstrsql + ' order by lastpost desc'endelsebeginset gstrsql = 'select top ' + str(tpp)+ '* from dnt_topics where tid < (select min (