数据库分页SQL语句

我们在编写MIS系统和Web应用程序等系统时，都涉及到与数据库的交互，如果数据库中数据量很大的话，一次检索所有的记录，会占用系统很大的资源，因此我们常常采用，需要多少数据就只从数据库中取多少条记录，即采用分页语句。根据自己使用过的内容，把常见数据库Sql Server,Oracle和My sql的分页语句，从数据库表中的第M条数据开始取N条记录的语句总结如下：

SQL Server 
        从数据库表中的第M条记录开始取N条记录，利用Top关键字：注意如果Select语句中既有top，又有order by，则是从排序好的结果集中选择：
        SELECT *
        FROM  ( SELECT Top N *  
                         FROM  (SELECT Top (M + N - 1) * FROM 表名称 Order by 主键 desc) t1 ) t2
        Order by 主键 asc

        例如从表Sys_option（主键为sys_id)中从10条记录还是检索20条记录，语句如下：
        SELECT * 
        FROM ( SELECT TOP 20 *
                        FROM (SELECT TOP 29 * FROM Sys_option order by sys_id desc) t1) t2
        Order by sys_id asc

        Oralce数据库
        从数据库表中第M条记录开始检索N条记录
        SELECT * 
        FROM (SELECT ROWNUM r,t1.* From 表名称 t1 where rownum < M + N) t2
        where t2.r >= M
        例如从表Sys_option（主键为sys_id)中从10条记录还是检索20条记录，语句如下：
        SELECT * 
        FROM (SELECT ROWNUM R,t1.* From Sys_option where rownum < 30 ) t2
         Where t2.R >= 10

        My sql数据库
        My sql数据库最简单，是利用mysql的LIMIT函数,LIMIT [offset,] rows从数据库表中M条记录开始检索N条记录的语句为：
        SELECT * FROM 表名称 LIMIT M,N

         例如从表Sys_option（主键为sys_id)中从10条记录还是检索20条记录，语句如下：
         select * from sys_option limit 10,20


MSSQL存储过程分页
附存储过程

/* QQ312430633 创建日期:2008-06-25 */ 
--修改了ORDER BY 需要 percent 与 ORDERBY 失效的BUG--
---注意'roder by'用一个空格间隔

Create PROCEDURE  [dbo].[les_AllowPaging] 
@pageindex int,    ----*****页码
@PageSize int,     ----*****每页显示条数
@tsql varchar(4000)----*****SQL语句
as

Declare @SqlSelect  varchar(4000)
Declare @orderby  varchar(4000)
Declare @AllowPagingSql  varchar(4000) 

---判断是否排序
if CHARINDEX('order by',@tsql) <> 0
begin
    set @SqlSelect=replace(substring (@tsql,1, CHARINDEX('order by',@tsql)-1),'$','''')  
    set @orderby=replace(substring (@tsql, CHARINDEX('order by',@tsql),len(@tsql) ),'$','''')      
    set @AllowPagingSql=
        'select * from (SELECT  ROW_NUMBER() OVER('+@orderby+') AS AllowPagingId,* FROM ('+
        @SqlSelect
        +') as table1) as table2 where AllowPagingId between '
        +convert(varchar(10),((@pageindex-1) * @PageSize+1))+' and '
        +convert(varchar(10), @pageindex * @PageSize)         
    exec  (@AllowPagingSql)
end 
else
begin
    set @SqlSelect=replace(@tsql,'$','''')  
    set @orderby=''
    set @AllowPagingSql=
        'select *  from (SELECT  *,ROW_NUMBER() OVER(ORDER BY orderbyID DESC) AS AllowPagingId FROM  ( select *, 1 as orderbyID from ( '
        +@SqlSelect
        +' )  as  tbs1 )   as Tabl1 ) as table2 where AllowPagingId between '
        +convert(varchar(10),((@pageindex-1) * @PageSize+1))+' and '
        +convert(varchar(10), @pageindex * @PageSize)         
        exec  (@AllowPagingSql)
end

set @AllowPagingSql='select 
case 
     when count(*)%'+convert(varchar(10),@PageSize)+'=0 then count(*)/'+convert(varchar(10),@PageSize)+'
     when count(*)%'+convert(varchar(10),@PageSize)+'<>0 then count(*)/'+convert(varchar(10),@PageSize)+'+1
end as pageCount,count(*) as RowsCount from ('+@SqlSelect+') as tab1'
exec  (@AllowPagingSql)

靠UI分页只是一部分

如果一个查询的结果数据量巨大，就需要数据查询的时候就分页，每次只返回其中一页

第一种方法：
思路是根据页号取出两部分数据，比如取出前90条，然后取出前100条，然后比较取出两次结果的差集。

在30万条记录的情况下，如果只分100页（结果有10000条记录），大约需要1分半钟。索引建得好的话，1分钟左右。


//select * from //这一句是不能修改的了，因为它是从结果中读取，所以必须要用*
//(select top @h_count (@filedlist) from @tableName .....) as big //取出符合条件的上限的记录
//where
//big.guid   //这里是关键，根据主键从下限结果中过滤掉重复的记录（只留下不同的数据，也就是求交集）
//not in
//(select top @l_count guid from @table .....)//下限
//order @orderby  //原来的格式，这里只保留了orderby之后的，应该保留条件之后所有的，包括gruopby什么的

函数类似如此：
  public string MakeSqlPager(string sourceSql,int pageIndex)
  {
   //使用默认页面大小
   string orderbyStr=sourceSql.Substring(sourceSql.ToLower().IndexOf("order by"));
   int index=sourceSql.ToLower().IndexOf("select");
   string bigRes="("+ sourceSql.Insert(index+6," top "+((pageIndex+1)*_pageSize).ToString()+" ")+") as big";
   string smallRes="("+ sourceSql.Insert(index+6," top "+(pageIndex*_pageSize).ToString()+" ")+")";
   return "select * from "+bigRes+" where big.guid not in "+smallRes+" "+orderbyStr;
  }

这种方法还可以改进，就是第二次取过滤时从第一个的结果里面过滤。

第二种方法：
掐头去尾，程序还没写
SELECT * FROM
(
  SELECT TOP 100 * FROM
  (
    SELECT TOP 100000 * FROM pagetest ORDER BY regt ASC
   ) as a
  ORDER BY regt desc
) as b
 ORDER BY regt ASC

测试了一下，大约用时间29秒。

比较：
第一种方法的效率很低，猜测是因为多次需要循环比较，时间复杂度要高一个等级。比如，这种方法的响应时间和所取得的页号有很大关系。
第二种方法还是可以接受的，和页号无关，但是也需要两次比较

网上还有使用比较ID的方法的，但是不是所有的表都有ID，即使有，也不一定是int类型的。
还有用存储过程创建临时表的，我还没有测试效率如何

sqlserver里面没有rownum 这个功能（最新的2005beta2版本据说有了，Oracle里面有），所以一次比较就能分页的算法还真不好写

三种SQL分页法效率分析

表中主键必须为标识列，[ID] int IDENTITY (1,1)
　　1.分页方案一：(利用Not In和SELECT TOP分页)
　　语句形式：




 SELECT TOP 10 *
 FROM TestTable
 WHERE (ID NOT IN
           (SELECT TOP 20 id
          FROM TestTable
          ORDER BY id))
 ORDER BY ID
 
 
 SELECT TOP 页大小 *
 FROM TestTable
 WHERE (ID NOT IN
           (SELECT TOP 页大小*页数 id
          FROM 表
          ORDER BY id))
 ORDER BY ID
　　2.分页方案二：(利用ID大于多少和SELECT TOP分页)
　　语句形式：




   SELECT TOP 10 *
 FROM TestTable
 WHERE (ID >
           (SELECT MAX(id)
          FROM (SELECT TOP 20 id
                  FROM TestTable
                  ORDER BY id) AS T))
 ORDER BY ID
 
 
 SELECT TOP 页大小 *
 FROM TestTable
 WHERE (ID >
           (SELECT MAX(id)
          FROM (SELECT TOP 页大小*页数 id
                  FROM 表
                  ORDER BY id) AS T))
 ORDER BY ID


3.分页方案三：(利用SQL的游标存储过程分页)




 create  procedure SqlPager
 @sqlstr nvarchar(4000), --查询字符串
 @currentpage int, --第N页
 @pagesize int --每页行数
 as
 set nocount on
 declare @P1 int, --P1是游标的id
  @rowcount int
 exec sp_cursoropen @P1 output,@sqlstr,@scrollopt=1,@ccopt=1,@rowcount=@rowcount output
 select ceiling(1.0*@rowcount/@pagesize) as 总页数--,@rowcount as 总行数,@currentpage as 当前页 
 set @currentpage=(@currentpage-1)*@pagesize+1
 exec sp_cursorfetch @P1,16,@currentpage,@pagesize 
 exec sp_cursorclose @P1
 set nocount off
　　其它的方案：如果没有主键，可以用临时表，也可以用方案三做，但是效率会低。
　　建议优化的时候，加上主键和索引，查询效率会提高。
　　通过SQL 查询分析器，显示比较：我的结论是:
　　分页方案二：(利用ID大于多少和SELECT TOP分页)效率最高，需要拼接SQL语句
　　分页方案一：(利用Not In和SELECT TOP分页) 效率次之，需要拼接SQL语句
　　分页方案三：(利用SQL的游标存储过程分页) 效率最差，但是最为通用