原文页类的代码如下:
<script language="vbscript" runat="server">
'----------------------------------------
' 数据库分页类 for sqlserver.
' 作者: Robin_Zhang ([email protected])
' 请保留此信息，谢谢。
'----------------------------------------
class splitpage
public curr_page '//当前页码
public istart '//分页开始的记录位置
public iend '//分页结束的记录位置
public countpage '//页数
public conn '//数据库连接对像
public perpagecount '//每页显示的记录个数
public countrecord '//总记录数
public tablename '//表或视图名称
public key '//主键
public condition '//查询条件
public orderby '//排序条件
public sql_count '//求总记录数的sql语句
public sql_search '//查询sql语句
public split_record '//得到的分页记录集
public url '//当前页面的url
public bar1 '//翻页条
public bar2 '//翻页条
public solitude_sql '传送单独的sql语句
'//-------------------private 内部方法集合-----------------------
'得到查询的记录数,总页数,sql语句,得到当前页数
private sub getCountRecord()
if isnull(solitude_sql) or isempty(solitude_sql) then
sql = "select count("& key &") as totalvalue from "& tablename & condition
else
sql = "select count(*) as totalvalue from ("&solitude_sql&") temptablea"
end if
' response.write "<hr>"&sql
' response.end
set rs = conn.execute(sql)
countrecord = rs("totalvalue")
rs.close
set rs=nothing
sql_count = sql
countpage = abs(int(-(countrecord/perpagecount)))
if curr_page < 1 then curr_page = 1
if curr_page >countpage then curr_page = countpage
end sub
'根据当前页码和每页显示的个数算出起始位置
private sub count_istart()
istart = clng((curr_page-1) * perpagecount)
if istart > clng(countrecord) then istart = clng(countrecord)
if istart < 0 then istart = 0
end sub
'根据当前页码和每页显示的个数算出结束位置
private sub count_iend()
iend = clng(istart + perpagecount)
if iend > clng(countrecord) then iend = clng(countrecord)
end sub
private function get_record()
if isnull(solitude_sql) or isempty(solitude_sql) then
sql = "select * from "& tablename &" where "& key &" not in (select top "& istart &" "& key &" from "& tablename & condition & orderby &") and "& key &" in (select top "& iend &" "& key &" from "& tablename & condition & orderby & ")" & orderby
else
sql = "select * from ("& solitude_sql &")a where "& key &" not in (select top "& istart &" "& key &" from ("& solitude_sql &")b"& condition & orderby &") and "& key &" in (select top "& iend &" "& key &" from ("& solitude_sql &")c"& condition & orderby & ")" & orderby
end if
'response.write sql
sql_search = sql
set get_record = conn.execute(sql)
end function
private sub get_url()
url = Request.ServerVariables("url") & "?" & Request.ServerVariables("query_string")
pos = InStrRev(url,"&page")-1
if pos > 0 then
url = mid(url,1,pos)
end if
if Request.ServerVariables("query_string") = "" then
url = Request.ServerVariables("url") & "?1=1"
end if
end sub
private function get_bar_gb2312()
bar1 = "目前为第 "& curr_page &"页 共计 "& countpage &" 页 共有 "& countrecord &" 笔资料"
a1 = "<a href='"& url &"&page=1'>首页</a> "
a2 = "<a href='"& url &"&page="& curr_page-1 &"'>上页</a> "
a3 = "<a href='"& url &"&page="& curr_page+1 &"'>下页</a> "
a4 = "<a href='"& url &"&page="& countpage &"'>末页</a> "
curr_page = clng(curr_page)
if curr_page <=1 then
a1 = "首页 "
a2 = "上页 "
end if
if curr_page >= clng(countpage) then
a3 = "下页 "
a4 = "末页 "
end if
bar2 = a1 & a2 & a3 & a4
end function
private function get_bar_big5()
end function
'//---------------------public 供外部调用--------------------------
'存取一条sql
public sub set_solitude_sql(sql)
solitude_sql = sql
end sub
'取得数据库连接的方法
public sub setConn(o)
set conn = o
end sub
'设定每页显示记录的个数
public sub setPerpagecount(o)
perpagecount = o
end sub
'设定查询的表名或视图名称
public sub setTablename(atablename)
tablename = atablename
end sub
'设定主键
public sub setKey(akey)
key = akey
end sub
'设定查询条件
public sub setCondition(acondition)
condition = " " & acondition
end sub
'设定排序依据
public sub setOrderby(aorderby)
orderby = " " & aorderby
end sub
'设定当前页码
public sub setCurr_page(o)
if o <> "" then
curr_page = clng(o)
else
curr_page = 1
end if
end sub
public sub execute()
call getCountRecord()
call count_istart()
call count_iend()
call get_url()
set split_record = get_record()
call get_bar_gb2312()
end sub
end class
</script>
以前的语句中有一个not in和一个in
select * from table_name where id not in (select top 100 id from table_name order by id desc) and id in (select top 200 id from table_name order by id desc) order by id desc
考验到in会遍历整张表.所以做以下修改...
select top 10 * from table_name where id>(select max (id) from (select top ((页码-1)*10) id from table_name order by id) as table_temp) order by id
|
本类阅读TOP10