Oracle Pagination

最新推荐文章于 2023-11-21 10:17:09 发布

weixin_30625691

最新推荐文章于 2023-11-21 10:17:09 发布

阅读量92

点赞数

CC 4.0 BY-SA版权

文章标签：数据库

原文链接：http://www.cnblogs.com/garinzhang/archive/2012/12/12/2814261.html

本文深入探讨了SQL中使用rownum进行分页查询的优化策略与实践，包括如何正确构造SQL语句以提高查询效率，以及不同场景下分页查询的实现方式对比。重点介绍了分页查询的常见问题、解决方法以及SQL语句的优化技巧。

rownum为虚拟列，不是实际物理列

select rownum,t.code from sys_config t; // 取得行数和code列

select rownum, t.code from sys_config t where rownum <=20 order by t.code; // 取得行数<=20，并按code排序，排序失败

select rownum, code from (select * from sys_config order by code) where rownum <=20; // 取得前20行成功

select rownum, code from (select * from sys_config order by code) where rownum>=1 and rownum <=20; // 取得1到20行成功

select rownum, code from (select * from sys_config order by code) where rownum>=2 and rownum <=20; // 取得2到20行失败

select rn,code from (select rownum as rn,code from (select * from sys_config order by code)) where rn >=2 and rn <=20; // 取得2到20行成功

最后一行为分页查询SQL语句，其他分页语句：
select * from (select rownum as rn, T.* from (select * from table_name) T where rownum<40) where rn>21; // 效率高些
select * from (select rownum as rn, T.* from (select * from table_name) T) where rn between 20 and 40; // 效率低些

参考连接文章：

http://database.51cto.com/art/201106/270156.htm

http://database.51cto.com/art/200904/118737.htm

转载于:https://www.cnblogs.com/garinzhang/archive/2012/12/12/2814261.html