我写的分页代码

网络整理 - 08-16

access 测试地址：

基本不受记录大小限制，15毫秒左右（access） sql存储过程的几毫秒或0毫秒
?forum_id=3&view_id=54183


<%

sub works_list(pagesize,nsort,table,field,sqlwhere)
dim recordcount,pagecount,pageno,i
recordcount=application("recordcount"&sqlwhere)
if recordcount="" then
sql="select count(id) from works "&sqlwhere
set rs=conn.execute(sql)
recordcount=rs(0)''取得记录总数
application("recordcount"&sqlwhere)=recordcount
end if
pagecount=((recordcount-1)\pagesize)+1''总页数
pageno=trim(request("pageno"))''当前页

if isempty(pageno) or not isnumeric(pageno) then
pageno=1
else
pageno=pageno
end if
if pageno<1 then
pageno=1
end if

if pageno*10 >= pagecount*10 then
pageno=pagecount
end if

if recordcount>0 then

call get_pageid(1,100,pagesize,pageno,table,sqlwhere,pagecount)

end if
response.write "<table100%''><tr height=30 ><td width=50>序号</td><td width=230>作品名称</td><td width=80>作者</td><td width=100>创作日期</td></tr>"
i=0

If dblink=1 then
Sql="select top "&pagesize&" "&field&" from "&table&" "&sqlwhere
set rs=conn.execute(sql)
else
cmd.commandtext="page_list"
cmd.parameters("@pagesize")=pagesize
cmd.parameters("@table")=table
cmd.parameters("@field")=field
cmd.parameters("@sqlwhere")=sqlwhere
set rs=cmd.execute
end if
do while not rs.eof
i=i+1
response.write "<tr height=22><td >"&i&"</td><td><a href=''works_view.asp?id="&rs("id")&"''>"&codehtml(rs("name"),1,18)&"</a></td><td><a href=''author_view.asp?id="&rs("authorid")&"''>"&codehtml(rs("author"),1,0)&"</a></td><td>"&year(rs("finishdate"))&"年"&month(rs("finishdate"))&"月"&day(rs("finishdate"))&"日</td></tr>"
rs.movenext
loop

response.write "<formpagelist'' method=get action=''?''><tr height=30><td colspan=6 ><div align=center>共<font color=#FF0000>"&pagecount&"</font>页<font color=#FF0000>"&recordcount&"</font>条数据， <a href=''?s_key="&s_key&"&s_field="&s_field&"&nsort="&nsort&"&author="&authorid&"&id="&sortid&"&pageno=1''>首页</a> <a href=''?s_key="&s_key&"&s_field="&s_field&"&nsort="&nsort&"&author="&authorid&"&id="&sortid&"&pageno="&pageno-1&"''>上一页</a> <a href=''?s_key="&s_key&"&s_field="&s_field&"&nsort="&nsort&"&author="&authorid&"&id="&sortid&"&pageno="&pageno+1&"''>下一页</a> <a href=''?s_key="&s_key&"&s_field="&s_field&"&nsort="&nsort&"&author="&authorid&"&id="&sortid&"&pageno="&pagecount&"''>尾页</a> "

response.write "跳转到第<inputpageno'' type=text size=3 maxlength=''10'' value="&pageno&">页 "&_
"<input type=''submit'' value=''go''>"&_
"<inputs_key'' type=hidden value=''"&s_key&"''>"&_
"<inputs_field'' type=hidden value=''"&s_field&"''>"&_
"<inputnsort'' type=hidden value=''"&nsort&"''>"&_
"<inputauthor'' type=hidden value="&authorid&">"&_
"<inputid'' type=hidden value="&sortid&">"&_
"</div></td></tr></form></table>"
end sub
''****************************************取得页边界*********************************************

sub get_pageid(n,k,pagesize,pageno,table,sqlwhere,pagecount)

dim pageid
select case n
case 1

redim pageid(pagecount)
if isempty(application("pageid"&sqlwhere)) then
set rs=server.createobject("adodb.recordset")
rs.open "select id from "&table&" "&sqlwhere,conn,0,1
if not rs.eof then
dim i
for i=0 to pagecount-1
pageid(i)=rs(0)
rs.move pagesize
next
application.Lock()
application("pageid"&sqlwhere)=pageid
application.UnLock()
end if
else
pageid=application("pageid"&sqlwhere)
end if

select case pageno
case pagecount
sqlwhere=sqlwhere&" and id>="&pageid(pageno-1)
case else
sqlwhere=sqlwhere&" and id>="&pageid(pageno-1)&" and id<"&pageid(pageno)
end select

case 2
redim pageid(k-1)
dim j''第几段
''根据pageno算出j的值
j=(pageno-1)\k

if isempty(application("pageid"&j&sqlwhere)) then
set rs=server.createobject("adodb.recordset")
if j=0 then
rs.open "select top "&pagesize*k&" id from "&table&" "&sqlwhere&" order by id desc",conn,0,1
else
rs.open "select top "&pagesize*k&" id from "&table&" "&sqlwhere&" and id <(select min(id) from (select top "&pagesize*k*j&" id from "&table&" "&sqlwhere&" order by id desc) as tb) order by id desc",conn,0,1
end if

if not rs.eof then
''分割执行，pagecount\k+1段，每段K页,算出每页pageid最大id
if j=pagecount\k then
i=0
do while not rs.eof
pageid(i)=rs(0)
i=i+1
rs.move pagesize
loop
else
for i=0 to k-1
pageid(i)=rs(0)
rs.move pagesize
next
end if
application.Lock()
application("pageid"&j&sqlwhere)=pageid
application.UnLock()

end if
else
pageid=application("pageid"&j&sqlwhere)
end if

if pageno mod k=0 or pageno=pagecount then
sqlwhere=sqlwhere&" and id<="&pageid(pageno-j*k-1)&" order by id desc"
else
sqlwhere=sqlwhere&" and id<="&pageid(pageno-j*k-1)&" and id>"&pageid(pageno-j*k)
end if

end select
end sub
''****************************************************************

sub insert_datas(n)
dim i
for i=1 to n
conn.execute("insert into works SELECT top 100000 name, remark1, remark2, sort, img1, img2, finishdate, putdate, demourl, downurl, is_open, authorid, author,userid,username, seenum, power, is_top FROM works)")
next
end sub
%>

今天改进完毕，性能超过网上可见的同类（未改为存储过程）