文档章节

Oracle 中的 SQL 分页查询原理和方法详解

几个栗子
 几个栗子
发布于 07/16 19:30
字数 1149
阅读 17
收藏 5

本文分析并介绍 Oracle 中的分页查找的方法。

    Oracle 中的表,除了我们建表时设计的各个字段,其实还有两个字段(此处只介绍2个),分别是 ROWID(行标示符)和 ROWNUM(行号),即使我们使用DESCRIBE命令查看表的结构,也是看不到这两个列的描述的,因为,他们其实是只在数据库内部使用的,所以也通常称他们为伪列(pseudo column)。

    下面我们先建表并添加一些数据来验证上面的说明。

     建表:

create table users(
id integer primary key,
name nvarchar2(20)
)

 

    插入数据:

 

insert into users(id,name) values(1,'tom');
insert into users(id,name) values(2,'cat');
insert into users(id,name) values(3,'bob');
insert into users(id,name) values(4,'anxpp');
insert into users(id,name) values(5,'ez');
insert into users(id,name) values(6,'lily');

 

  1. 使用describe命令查看表结构:

    可以看到,确实只有建表时的两个字段。

    但我们可以查询的时候,查找到伪列的值:

select rowid,rownum,id,name from users;

    结果:

   

    这个 rowid 我们一般用不到,Oracle 数据库内部使用它来存储行的物理位置,是一个 18 位的数字,采用 base-64 编码。

    而这个 rownum,我们也正是使用它来进行分页查询的,它的值,就是表示的该行的行号。

    对于分页,我们只要想办法可以查询到从某一起始行到终止行就可以的,分页的逻辑可以放到程序里面。

    于是,我们理所当然会想到如下语句查询第 2 页的数据(每页2条数据,页码从 1 开始,所以起始行的行号为 (页码-1) * 每页长度 +1=3,终止行的行号为 页码*每页长度=4 )。

select * from users where rownum>=3 rownum <= 4;

    哈哈!是不是发现没有任何结果,原因很简单,Oracle机制就是这样的:因为第一条数据行号为1,不符合>=3的条件,所以第一行被去掉,之前的第二行变为新的第一行(即这个行号不是写死的,可以理解为是动态的),如此下去,一直到最后一行,条件始终没法满足,所以就一条数据也查不出来。

    既然找到了原因,解决方法也就很明显了,我们只要将行号查询出来生成一个结果集,然后再从这个结果集中,选择行号大于我们设定的那个值就可以了,上面的分页查找正确的写法应该是这样:

select id,name from(
select rownum rn,u.* from users u) ua
where ua.rn between 3 and 4;

    上面的语句还可以优化:>= 不能用,但是 <= 是可以的,我们不需要在子查询中将结果全部查出来,首先使用终止行筛选子查询的结果,SQL如下:

select id,name from(
select rownum rn,u.* from users u where rownum<=4) ua
where ua.rn >= 3;

    结果:

   

    很多时候,我们并不是盲目的分页查找的,二十按某一个或多个字段的升序或降序分页,即包含 order by 语句的分页查询,我们先看一下 order by 的查询结果中 rownum 是怎样的:

select rownum,id,name from users order by name;

    结果:

   

    可以看到,我们说行号完全是动态的,也是不准确的,这时候的行号并不是经过 order by 后新结果的增序行号。

    但有了上面的嵌套查询的经验,这里也可以好好应用一下,怎么做呢:先查找出排序好的结果集,然后应用上面的方法得到最终结果,sql 如下:

select id,name from(
(select rownum rn,uo.* from
(select * from users u order by name) uo
where rownum<=4)) ua
where ua.rn>=3;

    按照上面的结果,正确的分页结果应该是 id 为 2 和 5 的,看下结果:

   

    OK,结果正确。

    其实连表查询之类的,也是差不多的,多点嵌套而已,掌握了原理,随便分析一下就能写出对应的 SQL 了,而编写 SQL 时,我们也得动动脑子,毕竟 SQL 也是由优劣之分的。

 

原文写得很好可以看下!

本文转载自:http://blog.csdn.net/anxpp/article/details/51534006

 

本文转载自:https://blog.csdn.net/anxpp/article/details/51534006

共有 人打赏支持
几个栗子
粉丝 5
博文 78
码字总数 38027
作品 0
杭州
程序员
加载中

评论(2)

几个栗子
几个栗子

引用来自“巴拉迪维”的评论

文章里面有几个图挂了,可以换一下。文章本事还是不错的。
知道了,谢谢 ~~
巴拉迪维
巴拉迪维
文章里面有几个图挂了,可以换一下。文章本事还是不错的。
mysql 和 oracle 实现分页原生 sql

比如使用 Java 开发的项目中操作数据库,那么现在和数据库交互的工作现在都用框架,比如 MyBatis 或者 Hibernate 但是他们不还是通过 JDBC 的方式访问数据库,那么我们就有必要根据数据库类型...

举个_栗子
07/16
0
0
数据库查询返回特定结果即分页查询

1 几种不同数据库的不同的分页写法: a mysql a) 查询前n条记录select * from table_name limit 0,nb) 查询第n条到第m条select * from table_name limit n,m b oracle a)查询前n条记录selec...

王小明123
2013/06/08
0
0
Mybatis3.4.x技术内幕(二十):PageHelper分页插件源码及原理剖析

PageHelper是一款好用的开源免费的Mybatis第三方物理分页插件,其实我并不想加上好用两个字,但是为了表扬插件作者开源免费的崇高精神,我毫不犹豫的加上了好用一词作为赞美。 原本以为分页插...

祖大俊
2016/09/10
3.1K
4
主流数据库分页查询介绍

1 背景概述 由于在项目中需要在页面上显示数量非常多的数据, 在进行数据库查询时首先会把所有的数据都查询出来,然后在进行显示,这时候分页查询的操作就必不可少了,本文介绍Mysql、Oracl...

数通畅联
2015/11/26
185
0
详解Oracle的几种分页查询语句

分页查询格式: SELECT * FROM (SELECT A.*, ROWNUM RN FROM (SELECT * FROM TABLE_NAME) A WHERE ROWNUM <= 40)WHERE RN >= 21 其中最内层的查询SELECT * FROM TABLE_NAME表示不进行翻页的原......

Sheamus
2015/03/20
0
1

没有更多内容

加载失败,请刷新页面

加载更多

下一页

python3.6 取整除法

python3.6 中取整除法运算逻辑如下: d 非零,那么商 q 满足这样的关系: a = qd + r ,且0 ≤ r n1=7//3#7 = 3*2 +1n2=-6.1//3#-7 = 3*(-3)+2'{},{}'.format(n1,n2) 从运行结果可以...

colinux
24分钟前
3
0
阶段总结——用虚拟机搭建一个高可用负载均衡集群架构

[toc] linux基本知识已经介绍完,现有一个业务需要操作,通过对这个项目的操作,可以复习、总结、巩固之前的知识点; ** 用13台虚拟机搭建一个高可用负载均衡集群架构出来,并运行三个站点,...

feng-01
27分钟前
0
0
mysql 设置utf8字符集 (CentOS)

1.查看数据库及mysql应用目前使用的编码方式 (1)链接mysql 客户端 (2)执行:status 结果: 2.修改mysql 应用的字符编码(server characterset ) (1)打开配置文件:vim /etc/mysql/my...

qimh
27分钟前
0
0
windows无法格式化u盘解决方法

1。点开始-运行-输入cmd-format f: /fs: fat32 (这里f:是指U盘所在盘符) 这个格式化会很慢 请耐心等待

大灰狼wow
39分钟前
0
0
MySql 8.0连接失败

原来,MySql 8.0.11 换了新的身份验证插件(caching_sha2_password), 原来的身份验证插件为(mysql_native_password)。而客户端工具Navicat Premium12 中找不到新的身份验证插件(caching_s...

放飞E梦想O
55分钟前
1
0

没有更多内容

加载失败,请刷新页面

加载更多

下一页

返回顶部
顶部