ORACLE分页查询SQL语法

当前位置：首页 > 范文|应用文 > IT技术专栏 > 数据库

ORACLE分页查询SQL语法

来源：易贤网阅读：778 次日期：2014-08-25 10:57:22

温馨提示：易贤网小编为您整理了“ORACLE分页查询SQL语法”,方便广大网友查阅！

--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; 

更多信息请查看IT技术专栏

上一篇：在oracle中导入导出dmp数据库文件

下一篇：Oracle中Clob类型处理解析

易贤网手机网站地址：ORACLE分页查询SQL语法

由于各方面情况的不断调整与变化，易贤网提供的所有考试信息和咨询回复仅供参考，敬请考生以权威部门公布的正式信息和咨询为准！