开发学院WEB开发ASP 浅谈ACCESS数据库技术的2种分页核心思想 阅读

浅谈ACCESS数据库技术的2种分页核心思想

 2009-05-04 10:40:33 来源:WEB开发网   
核心提示:access分页技术思想A: 程序代码select top 分页数 * from 表 where 主键 not in (select top 第几页*分页数 主键 from 表 where 过滤条件) ACCESS分页技术思想B: 程序代码select top 页大小 * from 表1 where 主键 <

access分页技术思想A:


 程序代码
select top 分页数 * from 表 where 主键 not in (select top 第几页*分页数 主键 from 表 where 过滤条件)
 


ACCESS分页技术思想B:


 程序代码
select top 页大小 * from 表1 where 主键 <(select min(主键) from (select top 页大小 主键 from 表1 order by 主键 desc) as tblTmp) order by 主键 desc
 

相比这两种设计思想,就大容量的数据而言,譬如一表几万条数据的显示的时候,就能明显的感觉出,前者比后者快了,因为前者不用建立后台临时表处理!,少了一个环节!不过简单的说,还是很容易理解的

打个比方,我一页要五行数据,现在是第三页,那么第几页应该等于需要要的页数乘以记录!

一个基于此思路的分页函数应用:


 程序代码

<%
Function ShowClass(page, Fcount, order, desc)  
   if page = "" or len(page) = 0 then page = 1  
    
   SkinStr = "" 
   pageStr = "" 
   SkinTatol = conn.execute("select count(*) from Skin")(0)  
    
   Filtwheres = "order by "&order" "&desc  
    
   if page > 1 then  
     SkinSQL = "select top "& Fcount " Skin_ID,Skin_Name,Skin_Designer,Skin_PubDate,Skin_DesignerURL,Skin_DesignerMail,Skin_Geterip,Skin_GetTime,LocalSkinInfoPReview,Skin_RandromNumber,Skin_DownCouns,Skin_FromURL from Skin where Skin_ID not in (select top "& ((page -1)* Fcount)" Skin_ID from Skin "&Filtwheres") " & Filtwheres  
   else  
     SkinSQL = "select top "& Fcount " Skin_ID,Skin_Name,Skin_Designer,Skin_PubDate,Skin_DesignerURL,Skin_DesignerMail,Skin_GeterIP,Skin_GetTime,LocalSkinInfoPreview,Skin_RandromNumber,Skin_DownCouns,Skin_FromURL from Skin " & Filtwheres  
   end if  
 
       '------------------ www.cncms.com 分页栏 ---------------------  
       total = SkinTatol '取总数  
       per = Fcount '每页显示条数  
       pages = total / per '总共的页数  
        
       If pages <> int(pages) then  
         pages = int(pages) + 1  
       Else 
         pages = pages  
       End If 
        
       If (page * per) >= total then  
         bn = total  
       Else 
         bn = page * per  
       End If 
 
       if pages <= 9 then  
         if page > 5 then  
           a = page - 4  
           b = page + 4  
           if b > pages then b = pages  
         else  
           a = 1  
           b = pages  
         end if  
       else  
         if page > 5 then  
           a = page - 4  
           b = page + 4  
           if b > pages then b = pages  
         else  
           a = 1  
           b = 9  
         end if  
       end if  
        
       '------------- start --------------------  
       pageStr = pageStr & "<table><tr>" 
       pageStr = pageStr & "<td><a href=""javascript:void(0);"" onclick=""IndexShow(1, "&per", '"&order"', '"&desc"', 'index')""><</a></td>" 
        
       For i = a to b  
         if page = i then  
           pageStr = pageStr & "<td>" & i & "</td>" 
         else  
           pageStr = pageStr & "<td><a href=""Javascript:void(0);"" onclick=""IndexShow("& i ", "&per", '"&order"', '"&desc"', 'index')"">" & i & "</a></td>" 
         end if  
       Next 
        
       pageStr = pageStr & "<td><a href=""javascript:void(0);"" onclick=""IndexShow("& pages ", "&per", '"&order"', '"&desc"', 'index')"">></a></td>" 
       pageStr = pageStr & "</tr></table>" 
       '------------- end --------------------  
        
        
        
       set SkinDB = conn.execute(SkinSQL)  
       if SkinDB.bof or SkinDB.eof then  
         SkinStr = SkinStr & "none" 
       else  
         web_len = 1  
         SkinStr = SkinStr & "<table>" 
         do while not SkinDB.eof  
           if web_len mod 4 = 0 then  
             Bleft = "" 
             Bright = "</tr>" 
           elseif web_len mod 4 = 1 then  
             Bleft = "<tr>" 
             Bright = "" 
           else  
             Bleft = "" 
             Bright = "" 
           end if  
             SkinStr = SkinStr & Bleft & "<td><a href=""javascript:;"" onmouSEOver=""this.className='skinb'"" class=""skina"" onmouseout=""this.className='skina'"" onFocus=""this.blur()""><p class=""skinp""><img src=""GetSkinsFolder/Preview/"&SkinDB("Skin_Name")"_"&SkinDB("Skin_RandromNumber")".jpg"" onerror=""this.src='http://www.cncms.com/GetSkinsFolder/Preview/Preview.jpg'"" class=""viewimgskin""><br>"&SkinDB("Skin_Name")"</p></a></td>" & Bright  
           web_len = web_len + 1  
         SkinDB.movenext  
         loop  
         SkinStr = SkinStr & "</table>" 
       end if  
       ShowClass = pageStr & SkinStr  
End Function
%>

Tags:ACCESS 数据库 技术

编辑录入:爽爽 [复制链接] [打 印]
[]
  • 好
  • 好的评价 如果觉得好,就请您
      0%(0)
  • 差
  • 差的评价 如果觉得差,就请您
      0%(0)
赞助商链接