ORACLE分页查询SQL语法

luhaojian

贡献于2013-05-23

字数:2525 关键词: Oracle 数据库服务器 SQL

ORACLE分页查询SQL语法 2009-11-30 13:48:53 作者:佚名(543)  繁體中文 字号:T|T 我要收藏或分享到: [导读]--1:无ORDER BY排序的写法。(效率最高) ­--(经过测试,此方法成本最低,只嵌套一层,速度最快!即使查询的数据量再大,也几乎不受影响,速度依然!) ­SELECT * ­ FROM (Select ROWNUM AS ROWNO, T.* ­ from k_task T ­ where Flight_date between to_date(200 --1:无ORDER BY排序的写法。(效率最高) ­ --(经过测试,此方法成本最低,只嵌套一层,速度最快!即使查询的数据量再大,也几乎不受影响,速度依然!) ­ 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:有ORDER BY排序的写法。(效率最高) ­ --(经过测试,此方法随着查询范围的扩大,速度也会越来越慢哦!) ­ 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代替) ­ --(此方法随着查询数据量的扩张,速度会越来越慢哦!) ­ 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; ­ ­ --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; ­ ­ ­ --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; ­ ­ --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; ­ - 资料引用:http://www.knowsky.com/540258.html 关注此文的读者还看过: 2010-9-27 12:21:35 OpenWorld将至 Oracle偷偷更新Solaris 10 2010-3-31 11:08:00 Oracle学习查询操作的笔记以及语句 2010-3-31 11:07:10 Oracle更新操作笔记以及语句 2010-3-31 10:51:09 Oracle表管理以及约束笔记 2010-3-25 17:10:50 Oracle 10G关于表操作常用语句详解及随想 2010-3-9 17:43:26 如何用oracle多表创建的视图insert 2010-3-9 17:40:54 教你修改Oracle数据库的连接数 2009-12-15 17:07:13 Oracle to_char 函数详解

下载文档,方便阅读与编辑

文档的实际排版效果,会与网站的显示效果略有不同!!

需要 3 金币 [ 分享文档获得金币 ]
0 人已下载

下载文档

相关文档