分页语句,分享三种高效率SQL语句分页方法

本文实例汇总了Oracle实现分页查询的SQL语法,整理给大家供大家参考之用,详情如下:

常用的Oracle查询语句

1.如果有一个自动增长的id字段,则:

1.无ORDER BY排序的写法。(效率最高)

1.无ORDER BY排序的写法。(效率最高)

定义二个变量:Page,PageCount

经过测试,此方法成本最低,只嵌套一层,速度最快!即使查询的数据量再大,也几乎不受影响,速度依然!

经过测试,此方法成本最低,只嵌套一层,速度最快!即使查询的数据量再大,也几乎不受影响,速度依然!

Select top PageCount * From [tb_code] Where id>=(select min(id)
from (select top (Page-1)*PageCount+1 id from [tb_code] order by id
desc) as t) order by id desc

sql语句如下:

sql语句如下:

原理,根据ID计算出(Page-1)页的最小值,然后用TOP关键字及可解决问题。

SELECT *
 FROM (Select ROWNUM AS ROWNO, T.*
      from k_task T
     where Flight_date between to_date('20060501', 'yyyymmdd') and
        to_date('20060731', 'yyyymmdd')
      AND ROWNUM <= 20) TABLE_ALIAS
WHERE TABLE_ALIAS.ROWNO >= 10;

SELECT *
        FROM (Select ROWNUM AS ROWNO, T.*
       from k_task T
      where Flight_date between to_date(‘20060501’, ‘yyyymmdd’) and
       to_date(‘20060731’, ‘yyyymmdd’)
       AND ROWNUM <= 20) TABLE_ALIAS
WHERE TABLE_ALIAS.ROWNO >= 10;

2.SELECT TOP 10 id,username From [tb_code] where id not in
( SELECT TOP 20000 id FROM tb_code ORDER BY username)

2.有ORDER BY排序的写法。(效率最高)

2.有ORDER BY排序的写法。(效率最高)

优点:此方法可以根据表中的任一个字段排序,在一个表中有几百万条记录时,仍有很高的效率,缺点是在大数据量的情况下效率略逊于第一种

经过测试,此方法随着查询范围的扩大,速度也会越来越慢!

经过测试,此方法随着查询范围的扩大,速度也会越来越慢! 

3.SELECT TOP 10 id,username From
(SELECT TOP page*pagecount id, username FROM tb_code ORDER BY
username)
DERIVEDTBL ORDER BY username DESC

sql语句如下:

SELECT *
       FROM (SELECT TT.*, ROWNUM AS ROWNO
         FROM (Select t.*
         from k_task T
        where flight_date between to_date(‘20060501’, ‘yyyymmdd’)
and
       to_date(‘20060531’, ‘yyyymmdd’)
      ORDER BY FACT_UP_TIME, flight_no) TT
WHERE ROWNUM <= 20) TABLE_ALIAS
where TABLE_ALIAS.rowno >= 10;

优点:此方法可以根据表中的任一个字段排序.
缺点是效率最低

SELECT *
 FROM (SELECT TT.*, ROWNUM AS ROWNO
      FROM (Select t.*
          from k_task T
          where flight_date between to_date('20060501', 'yyyymmdd') and
             to_date('20060531', 'yyyymmdd')
          ORDER BY FACT_UP_TIME, flight_no) TT
     WHERE ROWNUM <= 20) TABLE_ALIAS
where TABLE_ALIAS.rowno >= 10;

3.无ORDER BY排序的写法。(建议使用方法1代替) 此方法随着查询数据量的扩张,速度会越来越慢!  

在SQL Server 2005中使用rownum分页(rownum函数使用方法介绍)

**3.无ORDER BY排序的写法。(建议使用方法1代替)

    

比方说要从表USER中查看第10到第20条之间的数据,那么SQL是这样实现的

**此方法随着查询数据量的扩张,速度会越来越慢!

SELECT *
      FROM (Select ROWNUM AS ROWNO, T.*
      from k_task T
       where Flight_date between to_date(‘20060501’, ‘yyyymmdd’) and
          to_date(‘20060731’, ‘yyyymmdd’)) TABLE_ALIAS
         WHERE TABLE_ALIAS.ROWNO <= 20
          AND TABLE_ALIAS.ROWNO >= 10;
TABLE_ALIAS.ROWNO between 10 and 100;

SELECT * FROM (SELECT rownum rowcount,USER.* FROM USER) WHERE ROWCOUNT
>=10 AND ROWCOUNT <20

sql语句如下:

4.有ORDER BY排序的写法.(建议使用方法2代替)

其中函数ROWNUM,用来记录每一条数据所在的位置。

SELECT *
 FROM (Select ROWNUM AS ROWNO, T.*
      from k_task T
     where Flight_date between to_date('20060501', 'yyyymmdd') and
        to_date('20060731', 'yyyymmdd')) TABLE_ALIAS
WHERE TABLE_ALIAS.ROWNO <= 20
  AND TABLE_ALIAS.ROWNO >= 10;
TABLE_ALIAS.ROWNO between 10 and 100;

此方法随着查询范围的扩大,速度也会越来越慢!   
  

您可能感兴趣的文章:

  • 高效的SQLSERVER分页查询(推荐)
  • mysql分页原理和高效率的mysql分页查询语句
  • sqlserver2005使用row_number()
    over分页的实现方法
  • MySQL
    百万级分页优化(Mysql千万级快速分页)
  • SQL SERVER 2008
    中三种分页方法与比较
  • mysql
    limit分页优化方法分享
  • oracle,mysql,SqlServer三种数据库的分页查询的实例
  • php+mysql分页代码详解
  • Oracle实现分页查询的SQL语法汇总
  • java分页拦截类实现sql自动分页

4.有ORDER BY排序的写法.(建议使用方法2代替)

 

此方法随着查询范围的扩大,速度也会越来越慢!

SELECT *
FROM (SELECT TT.*, ROWNUM AS ROWNO
FROM (Select *
from k_task T
where flight_date between to_date(‘20060501’, ‘yyyymmdd’) and
to_date(‘20060531’, ‘yyyymmdd’)
ORDER BY FACT_UP_TIME, flight_no) TT) TABLE_ALIAS
where TABLE_ALIAS.rowno BETWEEN 10 AND 20;

sql语句如下:

5.另类语法。(有ORDER BY写法)

SELECT *
 FROM (SELECT TT.*, ROWNUM AS ROWNO
      FROM (Select *
          from k_task T
          where flight_date between to_date('20060501', 'yyyymmdd') and
             to_date('20060531', 'yyyymmdd')
          ORDER BY FACT_UP_TIME, flight_no) TT) TABLE_ALIAS
where TABLE_ALIAS.rowno BETWEEN 10 AND 20;

该语法风格与传统的SQL语法不同,不方便阅读与理解,为规范与统一标准,不推荐使用。此处贴出代码供大家参考之用。

5.另类语法。(有ORDER BY写法)

 

该语法风格与传统的SQL语法不同,不方便阅读与理解,为规范与统一标准,不推荐使用。此处贴出代码供大家参考之用。

With partdata as(
SELECT ROWNUM AS ROWNO, TT.* FROM (Select *
from k_task T
数据库,where flight_date between to_date(‘20060501’, ‘yyyymmdd’) and
to_date(‘20060531’, ‘yyyymmdd’)
ORDER BY FACT_UP_TIME, flight_no) TT
WHERE ROWNUM <= 20)
Select * from partdata where rowno >= 10;

sql语句如下:

6.另类语法 。(无ORDER BY写法)

With partdata as(
 SELECT ROWNUM AS ROWNO, TT.* FROM (Select *
         from k_task T
         where flight_date between to_date('20060501', 'yyyymmdd') and
            to_date('20060531', 'yyyymmdd')
         ORDER BY FACT_UP_TIME, flight_no) TT
  WHERE ROWNUM <= 20)
  Select * from partdata where rowno >= 10;

With partdata as(
Select ROWNUM AS ROWNO, T.*
From K_task T
where Flight_date between to_date(‘20060501’, ‘yyyymmdd’) and
To_date(‘20060531’, ‘yyyymmdd’)
AND ROWNUM <= 20)
Select * from partdata where Rowno >= 10;

6.另类语法 。(无ORDER BY写法)

With partdata as(
 Select ROWNUM AS ROWNO, T.*
  From K_task T
  where Flight_date between to_date('20060501', 'yyyymmdd') and
     To_date('20060531', 'yyyymmdd')
   AND ROWNUM <= 20)
  Select * from partdata where Rowno >= 10; 

相信本文所述代码能够对大家有一定的参考借鉴价值。

您可能感兴趣的文章:

  • Oracle分页查询的实例详解
  • mysql
    oracle和sqlserver分页查询实例解析
  • Oracle分页查询性能优化代码详解
  • Oracle、MySQL和SqlServe三种数据库分页查询语句的区别介绍
  • 详解SQLServer和Oracle的分页查询
  • mysql、mssql及oracle分页查询方法详解
  • oracle,mysql,SqlServer三种数据库的分页查询的实例
  • 详解oracle分页查询的基础原理