关于分页类的改进....

Dear 丶 2022-09-20 12:38 106阅读 0赞

原文页类的代码如下:  
  
<script language="vbscript" runat="server">

'----------------------------------------  
'     数据库分页类 for sqlserver.  
'     作者: Robin\_Zhang ([eway365@tom.com][eway365_tom.com])  
'     请保留此信息，谢谢。  
'----------------------------------------  
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

[eway365_tom.com]: mailto:eway365@tom.com