分页

 Mysql的分页的sql语句:

     方法一:使用COUNT(*)的方式
            

  SELECT COUNT(*) FROM foo WHERE b = 1; 
  SELECT a FROM foo WHERE b = 1 LIMIT 100,10;

 

    方法二:使用SQL_CALC_FOUND_ROWS            

  SELECT SQL_CALC_FOUND_ROWS a FROM foo WHERE b = 1 LIMIT 100, 10; 
  SELECT FOUND_ROWS(); 

      第二种方式调用SQL_CALC_FOUND_ROWS之后会将WHERE语句查询的行数放在FOUND_ROWS()

       之中，第二次只需要查询FOUND_ROWS()就可以查出有多少行了。

 

讨论这两种方法的优缺点：
首先原子性讲，第二种肯定比第一种好。第二种能保证查询语句的原子性，第一种当两个请求之间有额外的操作修改了表的时候，结果就自然是不准确的了。而第二种则不会。但是非常可惜，一般页面需要进行分页显示的时候，往往并不要求分页的结果非常准确。即分页返回的total总数大1或者小1都是无所谓的。所以其实原子性不是我们分页关注的重点。

 

sqlserver的分页的sql语句:

     分页方案一：(利用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

             sql语句句型:                

 SELECT TOP 页大小 *FROM TestTable 
      WHERE (
                        ID NOT IN
                      (SELECT TOP 页大小*页数 id  FROM 表 ORDER BY id)
                  )ORDER BY ID

 

 

        分页方案二：(利用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

             sql语句句型:

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

 
       分页方案三：(利用SQL的游标存储过程分页)
                  代码如下:
                    

 create procedure XiaoZhengGe
     @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

 

 

          其它的方案：如果没有主键，可以用临时表，也可以用方案三做，但是效率会低。
          建议优化的时候，加上主键和索引，查询效率会提高。
          通过SQL 查询分析器，显示比较：结论是:
                分页方案二：(利用ID大于多少和SELECT TOP分页）效率最高，需要拼接SQL语句
                分页方案一：(利用Not In和SELECT TOP分页) 效率次之，需要拼接SQL语句
                分页方案三：(利用SQL的游标存储过程分页) 效率最差，但是最为通用
                在实际情况中，要具体分析

SqlServer分页的存储过程:

            方法一:               

declare 
     @PageNo int ,
     @pageSize int;
     set @PageNo搜索 = 2
     set @pageSize=20 
  select * from (
       select row_number() over(order by object_id) rn,* from sys.objects
         ) tb
        where rn >(@PageNo-1)*@pageSize and rn <=@PageNo*@pageSize

 

 

         方法二:
                还有一种方法就是将排序字段作为变量，通过动态SQL 实现，可以改成存储过程。     

declare 
   @PageNo int ,
   @pageSize int;
declare
   @TableName varchar(128),
   @OrderColumns varchar(500),
   @SQL varchar(max); 
   set @PageNo = 2,
   set @pageSize=20,
   set @TableName = 'sys.objects'set 
   @OrderColumns = 'name ASC,object_id DESC'
   set @SQL = 'select * from (
        select row_number() over(order by '+@OrderColumns+' ) rn,* from ' +@TableName+')tb where rn >'+convert(varchar(50),(@PageNo-1)*@pageSize) +' and  rn <= '+convert(varchar(50),@PageNo*@pageSize)print @SQL exec(@SQL)

 

 

 Oracle分页的SQL语句：

         方法一:
                   --1.先查询出全量
                   --2.使用rownum，它不支持大于号，支持小于号
                   --3.把全量作为一个结果集，让rownum小于当前页的结束号，

                          查询出一个结果集，把rownum作为一个结果
                   --4.把第三步的查询的结果集作为一个表
                   --5.在外面套一层select,把里层的rw作为查询条件，让它大于起始行号

                   

select * from (select rownum rw, a.*
     from (
            select * from eb_item
          ) a
          where rownum < 16) b
       where b.rw > 10;