Oracle数据库中表连接, 子查询, 及分页查询

1. 多表关系

1.1 多表概念

数据库就像一个大文件夹，里面装着好多小表格，每个表格记一类事儿，比如”学生表”记学生信息，”课程表”记课程信息。但现实里这些事儿都是互相联系的，这就需要用”多表关系” 把它们串起来。

多表关系是什么呢？又为什么需要多表关系呢？

举个最简单的例子：假如你要记录学生的成绩，学生信息（姓名、年龄）和课程信息（课程名、学分）如果都放在一个表里，会出现什么问题？

学生id	姓名	年龄	课程id	课程名	学分
1	安琪拉	20	101	计算机基础	90
1	安琪拉	20	102	Oracle数据库	80
1	安琪拉	20	103	数据分析	87
2	妲己	21	101	计算机基础	77
2	妲己	21	103	数据分析	70
3	王昭君	22	101	计算机基础	93
3	王昭君	22	102	Oracle数据库	97

浪费存储空间
- 比如同一门课的信息会重复写很多次（每个选这门课的学生都得写一遍课程名），存储空间浪费比较严重
管理维护不方便
- 如果要把课程名”数据分析”修改为”数据统计与分析”，需要对表中每个选修这门课程的信息都要修改一遍

解决办法：把不同类的信息拆分到不同的表，再用”关系”连起来。

学生信息表

学生id 姓名年龄

1 安琪拉 20

2 妲己 21

3 王昭君 22

课程表

课程id 课程名

101 计算机基础

102 Oracle数据库

103 数据分析

通过”关系”把学生信息表和课程表连起来（具体方式课程中会讲解如何实现）

学生id 课程id

1 101

1 102

2 101

学生id	姓名	年龄
1	安琪拉	20
2	妲己	21
3	王昭君	22

课程id	课程名
101	计算机基础
102	Oracle数据库
103	数据分析

学生id	课程id
1	101
1	102
2	101

多表关系基本上可以分为三种：

一对一
一对多(多对一)
多对多

1.2 多表关系：一对一

一对一关系表在实际开发中应用起来比较简单，通常是用来做单表的拆分，也就是将一张大表拆分成两张小表，将大表中的一些基础字段放在一张表当中，将其他的字段放在另外一张表当中，以此来提高数据的操作效率。

一对一的应用场景：用户表(基本信息+身份信息)

基本信息：用户的ID、姓名、性别、手机号、学历
身份信息：民族、生日、身份证号、身份证签发机关，身份证的有效期(开始时间、结束时间)

如果在业务系统当中，对用户的基本信息查询频率特别的高，但是对于用户的身份信息查询频率很低，此时出于提高查询效率的考虑，我就可以将这张大表拆分成两张小表，第一张表存放的是用户的基本信息，而第二张表存放的就是用户的身份信息。

他们两者之间一对一的关系，一个用户只能对应一个身份证，而一个身份证也只能关联一个用户。

那么在数据库层面怎么去体现上述两者之间是一对一的关系呢？

一对一关系：在任意一方的表中新增一个字段(叫外键)，关联另外一方的主键字段，并且给外键字段设置唯一约束(UNIQUE)。

SQL代码：

-- 用户基本信息表
CREATE TABLE t_user( 
    id NUMBER  PRIMARY KEY,      -- 主键
    name VARCHAR2(20) NOT NULL,  -- 姓名
    gender CHAR(1),              -- 性别
    phone CHAR(11),              -- 电话
    degree VARCHAR2(10)          -- 学历
);

-- 用户身份信息表
create table t_user_card(
    id NUMBER  PRIMARY KEY,
    nationality VARCHAR2(10),    -- 民族   
    birthday DATE ,              -- 生日
    idcard CHAR(18),             -- 身份证号
    issued VARCHAR2(20),         -- 签发机关
    expire_begin DATE,           -- 有效期限-开始
    expire_end DATE ,            -- 有效期限-结束
    
    user_id NUMBER               -- 外键字段：用户id（关联t_user表中的id主键字段） 
);

1.3 多表关系：一对多

概念：一边是”1个”，另一边是”多个”。比如：一个班级有多个学生。

一对多要怎么实现呢？

在”多”的那一边的表里，增加一个字段(叫外键），指向”一”那边表的主键字段。

示例：

业主类型表t_ownertype

id(主键)	业主类型名称
101	居民
202	行政事业单位

业主表t_owners

id(主键)	业主名称	`业主类型id(外键)`	门牌号	水表编号	登记日期
1	范冰	`101`	1-1	30406	2015-04-12
2	王强	`101`	1-2	30407	2015-02-14

特点：“一”边的一个记录（比如：业主类型ID 101），可以对应“多”边的多个记录（范冰、王强等）。

SQL代码：

--业主类型
create table t_ownertype
(
    id number primary key,
    name varchar2(30)
);

--业主表
create table t_owners
(
    id number primary key,
    name varchar2(30),
    addressid number,
    housenumber varchar2(30),
    watermeter number,
    adddate date,
    
    ownertypeid number          -- 业主类型id(外键字段)。 关联t_ownertype表中id主键
);

解释：”一对多”和”多对一”本质是一样的，只是看从哪边出发，从业主看业主类型是”一对多”，从业主类型看业主是 “多对一”。

1.4 多表关系：多对多

多对多的关系在开发中属于也比较常见的。比如：学生和课程的关系，一个学生可以选修多门课程，一个课程也可以供多个学生选修。

示例：学生与课程的关系

关系：一个学生可以选修多门课程，一门课程也可以供多个学生选择
实现关系：建立第三张中间表，中间表至少包含两个外键，分别关联两方主键

把复杂的多对多关系拆分为：两个一对多关系

中间表像一座桥，把两边的 “多” 连接起来，每个桥的两端分别是两个表的一条记录。

SQL代码：

-- 学生表
create table t_student(
    id number primary key,
    name varchar2(10),
    no varchar2(10)
);

-- 课程表
create table t_course(
   id number primary key,
   name varchar2(10)
);

-- 学生课程表（中间表）
create table t_student_course(
   id number primary key,
   student_id number not null,
   course_id  number not null
);

2. 多表查询

2.1 概述

多表查询：查询时从多张表中获取所需数据。

单表查询的SQL语句：SELECT 字段1,字段2,... FROM 表名

要执行多表查询，只需要使用逗号分隔多张表即可。

如： SELECT 表1.字段1 , 表1.字段2 , ... , 表2.字段 , ... FROM 表1 , 表2 , ...

查询业主类型表和业主表中的数据：

SELECT * FROM t_ownertype , t_owners; 

此时,我们看到查询结果中包含了大量的结果集，总共30条记录，而这其实就是业主类型表所有的记录(3行)与业主表所有记录(10行)的所有组合情况，这种现象称之为笛卡尔积。

笛卡尔积：笛卡尔乘积是指在数学中，两个集合(A集合和B集合)的所有组合情况。

在多表查询时，需要消除无效的笛卡尔积，只保留表关联部分的数据

在SQL语句中，如何去除无效的笛卡尔积呢？只需要给多表查询加上连接查询的条件即可。

-- 添加多表关联条件： 业主表.业主类型id = 业主类型表.id
SELECT * FROM t_ownertype,t_owners WHERE t_owners.ownertypeid = t_ownertype.id;

现在我们初步认识了多表查询，而我们学习的多表查询可以分为：

内连接查询（重点）
自连接查询（作业中）
外连接查询（重点）
- 左外连接
- 右外连接
- 全外连接(完全外连接)
自然连接查询（了解）

2.2 内连接查询

内连接查询：是把多个表中符合条件的数据连接为一条数据，如果哪个表中存在不符合连接条件的数据，那么这些数据就会被过滤掉（不显示）

标准语法：

SELECT 列名1 , ... , 列名N
FROM  A表  INNER JOIN B表  ON 连接条件

说明：INNER JOIN:中的INNER可以省略不写

示例：查询显示业主编号，业主名称，业主类型名称

-- 标准写法：
SELECT ow.id, ow.name AS owner_name, ot.NAME AS type_name 
FROM t_owners ow INNER JOIN t_ownertype ot ON ow.ownertypeid = ot.id;

-- 写法2： 省略 INNER 关键字
SELECT ow.id, ow.name AS owner_name, ot.NAME AS type_name 
FROM t_owners ow JOIN t_ownertype ot ON ow.ownertypeid = ot.id;

-- 写法3： 使用 WHERE 代替 ON
SELECT ow.id, ow.name AS owner_name, ot.NAME AS type_name 
FROM t_owners ow,t_ownertype ot
WHERE ow.ownertypeid =ot.id;

查询结果：

多表查询时给表起别名：

Oracle遵循早期SQL标准（ANSI-89），表别名后面不能使用AS关键字，直接使用别名即可。

一旦为表起了别名，就不能再使用表名.字段方式来指定对应的字段了，此时只能够使用别名.字段方式来指定字段。

多表查询编写技巧：

-- 查询显示业主编号，业主名称，业主类型名称、地址名称
/*
   明确1： 确定要查询的数据有哪些？
              业主编号，业主名称，业主类型名称、地址名称
   
   明确2： 确定所查询的数据分别归属于哪张表？
              业主表 ：业主编号，业主名称    t_owners
              业主类型表 ： 业主类型名称     t_ownertype
              地址表 ：地址名称              t_address
    
   明确3： 确定表与与之间的关联关系（找主键和外键进行关联）
              业主表.ownertypeid(外键) = 业主类型表.id(主键)
              AND
              业主表.addressid(外键) = 地址表.id(主键)
*/

SELECT
	ow.id,
	ow.name AS owner_name,
	ot.name AS type_name,
	ad.name AS address_name
FROM
	t_owners ow , 
	t_ownertype ot,
	t_address ad
WHERE 
    ow.ownertypeid = ot.id AND ow.addressid = ad.id;
    
    
-- 标准的内连接写法：
SELECT
	ow.id,
	ow.name AS owner_name,
	ot.name AS type_name,
	ad.name AS address_name
FROM
	t_owners ow 
	JOIN t_ownertype ot ON ow.ownertypeid = ot.id 
	JOIN t_address ad ON ow.addressid = ad.id;

2.4 左外连接查询

左外连接查询：查出左边表(left outer join关键字前面的表)的所有数据，根据连接条件去右表中找相应匹配的数据。如果找到则显示出来，如果找不到则填充NULL显示。

左外连接语法结构：

SELECT 列名1, 列名2, ...
FROM  左表  LEFT OUTER JOIN  右表  ON  连接条件
-- LEFT OUTER JOIN ： 其中 OUTER 可以省略

示例：查询业主的账务记录，显示业主编号、名称、年、月、金额。如果此业主没有账务记录也要列出姓名。

分析：我们要查询这个结果，需要用到T_OWNERS（业主表），T_ACCOUNT（账单表）按照查询结果，业主表为左表、账单表为右表。

-- 写法1 ： 标准写法
SELECT ow.id, ow.name, 
       ac.year, ac.month, ac.money 
FROM t_owners ow LEFT OUTER JOIN t_account ac ON ow.id = ac.owneruuid;

-- 写法2 ： 使用WHERE
SELECT ow.id, ow.name, 
       ac.year, ac.month, ac.money 
FROM t_owners ow , t_account ac
WHERE ow.id = ac.owneruuid(+)   -- 如果是左外连接，就在右表所在的条件一端填上: (+)

2.5 右外连接查询

右外连接查询：查出右表的所有数据，根据连接条件去左表查找对应的数据，如果找到就显示，如果找不到则显示为NULL。

右外连接语法结构：

SELECT 列名1 , 列名2 , ...
FROM 左表  RIGHT OUTER JOIN  右表  ON 连接条件
-- RIGHT OUTER JOIN :  其中 OUTER 可以省略

示例：查询业主的账务记录，显示业主编号、名称、年、月、金额。如果账务记录没有对应的业主信息，也要列出记录。

-- 写法1 ： 标准写法
SELECT
	ow.id,
	ow.name,
	ac.year ,
	ac.month,
	ac.money
FROM
	t_owners ow RIGHT OUTER JOIN t_account ac 
	ON ow.id = ac.owneruuid;

-- 写法2 ： 使用WHERE
SELECT
	ow.id,
	ow.name,
	ac.year ,
	ac.month,
	ac.money
FROM
	t_owners ow , t_account ac
WHERE
	ow.id(+) = ac.owneruuid; -- 如果是右外连接，就在左表所在的条件一端填上：(+)

2.5 全连接查询

全连接查询，也称为：完全外连接查询。可以理解为完全外连接 = 左外连接+右外连接。

语法：

SELECT 列名1, 列名2, ....
FROM 表A FULL OUTER JOIN 表B ON 连接条件;
-- FULL OUTER JOIN ：其中的 OUTER 可以省略

示例：查询业主的账务记录，显示业主编号、名称、年、月、金额。

如果账务记录没有对应的业主信息，也要列出年、月、金额。
如果业主没有账务记录也要列出编号、名称。

SELECT
	ow.id,
	ow.name,
	ac.year ,
	ac.month,
	ac.money
FROM
	t_owners ow FULL OUTER JOIN t_account ac 
	ON ow.id = ac.owneruuid;

执行结果：

2.5 自然连接查询

自然连接：是在两张表中寻找那些数据类型和列名都相同的字段，然后自动地将他们连接起来，并返回所有符合条件按的结果。

语法：

-- 未指定两表之间的关系列
SELECT 字段1, 字段2, ...
FROM 表A NATURAL JOIN 表B;
   
-- 指定两表之间的关系列
SELECT 字段1, 字段2, ....
FROM 表A JOIN 表B 
USING(关系列);

示例：查询SCOTT用户下的emp表和dept表，通过deptno字段连接两表

-- 自然连接
select * from emp natural join dept;  

-- 自然连接using用来指定关系列
select * from emp join dept using(deptno);  

说明：开发中不推荐使用自然连接查询

-- 以下代码执行会报错 ： 不能有限定词
select e.empno, e.ename, e.job,
       d.deptno, d.dname 
from emp e join dept d using(deptno);

回顾上午内容：

-- 内连接查询
SELECT 字段1,字段2,....
FROM 表1 INNER JOIN 表2 ON 关联条件

SELECT 字段1,字段2,....
FROM 表1,表2,....
WHERE 关联条件

-- 示例： 查询业主信息及业主类型名字
SELECT ow.id , ow.name AS owner_name, 
       ot.name AS type_name
FROM t_owners ow INNER JOIN t_ownertype ot
     ON ow.ownertypeid = ot.id
     


-- 外连接查询：左外（查询时会以左边表为主表，查询左表中所有数据，去右边匹配符合条件数据）
SELECT 字段1,字段2,....
FROM  左表  LEFT JOIN 右表  ON  关联条件

SELECT 字段1,字段2,....
FROM 左表,右表
WHERE 左表.字段 = 右表.字段(+)
 
-- 外连接查询：右外
SELECT 字段1,字段2,....
FROM  左表  RIGHT JOIN 右表  ON  关联条件

SELECT 字段1,字段2,....
FROM 左表,右表
WHERE 左表.字段(+) = 右表.字段 
 
 
-- 外连接查询：全外
SELECT  字段1,字段2,....
FROM 左表 FULL JOIN 右表
     ON 关联条件
 
 
-- 自然连接查询 （前置要求：两张表中要有相同名字的字段及类型）
SELECT * FROM 表A NATRUAL JOIN 表B

SELECT * FROM 表A JOIN 表B USING(字段) --表A和表B使用using指定的字段做为连接

3. 子查询

3.1 概述

子查询也称为嵌套查询，就是指在一个查询语句中嵌套另一个查询语句。子查询通常被括在圆括号内，它可以出现在 SELECT、FROM、WHERE、HAVING 等子句中。子查询可以帮助用户更灵活地从数据库中获取所需的数据，实现一些复杂的查询逻辑。

-- 书写在WHERE中的子查询
SELECT  *  FROM   t1   WHERE  column1 =  ( SELECT  column1  FROM  t2 ... );

根据子查询结果的不同可分为：

标量子查询（子查询结果为单个值[一行一列]） – 即可以做为关联子查询也可做为非关联子查询
列子查询（子查询结果为一列，但可以是多行）
行子查询（子查询结果为一行，但可以是多列）
表子查询（子查询结果为多行多列[相当于子查询结果是一张表]）

子查询划分为：
1、关联子查询
       子查询不能做为一个独立的个体单独运行（子查询必须借助外部的SELECT查询才能执行）
       先执行外部的Select查询，再执行内部的子查询
2、非关联子查询   
       可以把子查询可以做为一个独立的个体单独运行
       先执行子查询，然后根据子查询的结果，再来执行外部的Select查询
   

3.2 标量子查询

子查询返回的结果是单个值(数字、字符串、日期等)，最简单的形式，这种子查询称为标量子查询。

常用的操作符： = 、 <>/!= 、 > 、 >= 、 < 、 <=

示例1：查询2012年1月用水量大于平均值的账单记录

SELECT * 
FROM t_account 
WHERE YEAR='2012' 
      AND MONTH='01'
      AND usenum > (
            SELECT avg(usenum) FROM t_account WHERE YEAR='2012' AND MONTH ='01'
            );

示例2：查询”王强”业主的所有账单记录

SELECT * 
FROM t_account 
WHERE owneruuid = (SELECT id FROM t_owners WHERE name='王强');

3.3 列子查询

子查询返回的结果是一列(可以是多行)，这种子查询称为列子查询。

常用的操作符：

操作符	描述
IN	在指定的集合范围之内，多选一
NOT IN	不在指定的集合范围之内
ANY	和子查询返回的任意一个值比较
ALL	和子查询返回的所有值比较

示例1：查询地址是”明兴花园”和”鑫源秋墅”的业主记录

SELECT * 
FROM t_owners  
WHERE addressid IN  
      (
        SELECT id FROM t_address WHERE name = '明兴花园' OR name ='鑫源秋墅')
      );

示例2：查询地址含有”花园”的业主的信息

SELECT * 
FROM t_owners 
WHERE addressid IN 
      (
       SELECT id FROM t_address WHERE name LIKE '%花园%'
      );

示例3：查询地址不含有”花园”的业主的信息

SELECT * 
FROM t_owners 
WHERE addressid NOT IN 
      (
       SELECT id FROM t_address WHERE name LIKE '%花园%'
      );

示例4：查询2012年账单中用水量大于2012年3月最大用水量的账单数据

-- 子查询：标量子查询（一行一列）
SELECT * 
FROM t_account 
WHERE YEAR='2012' 
      AND usenum > 
      (
       SELECT max(usenum) 
       FROM t_account 
       WHERE YEAR='2012' AND MONTH='03'
      );

-- all 
SELECT * 
FROM t_account 
WHERE YEAR='2012' 
      AND usenum > ALL
      (
       SELECT usenum 
       FROM t_account 
       WHERE YEAR='2012' AND MONTH='03'
      );

示例5：查询2012年账单中用水量大于2012年3月最小用水量的账单数据

SELECT * 
FROM t_account 
WHERE YEAR='2012' 
      AND usenum > 
      (
       SELECT min(usenum) 
       FROM t_account 
       WHERE YEAR='2012' AND MONTH='03'
      );

-- any
SELECT * 
FROM t_account 
WHERE YEAR='2012' 
      AND usenum > ANY
      (
       SELECT usenum 
       FROM t_account 
       WHERE YEAR='2012' AND MONTH='03'
      );

3.4 行子查询

子查询返回的结果是一行(可以是多列)，这种子查询称为行子查询。

常用的操作符：= 、<> 、IN 、NOT IN

示例：

select * from t_account 
where (owneruuid,ownertype) =
(select id , ownertypeid from t_owners where name = '王强');

3.5 表子查询

子查询返回的结果是多行多列，常作为临时表，这种子查询称为表子查询。

示例：查询2012年用水量大于平均值的账单信息和其业主信息

SELECT 
       ac.id,ac.YEAR,ac.MONTH,ac.usenum,ac.money, 
       ow.id,ow.name
FROM 
    (
     SELECT * FROM t_account 
     WHERE YEAR='2012' AND usenum > 
          (SELECT avg(usenum) FROM t_account WHERE YEAR='2012')
    ) ac LEFT JOIN t_owners ow
ON 
     ow.id = ac.owneruuid;
     
     
-- 另一种：
SELECT ac.id,ac.YEAR,ac.MONTH,ac.usenum,ac.money, 
       ow.id,ow.name
FROM t_account ac JOIN t_owners ow ON ac.owneruuid = ow.id
WHERE YEAR='2012' 
     AND usenum > (SELECT avg(usenum) FROM t_account WHERE YEAR='2012')

执行结果：

关联子查询：（依赖外部的查询）

子查询不能作为一个独立的个体单独运行
执行顺序：先执行外部的Select查询，再执行内部的子查询

-- 统计2012年各地区的总用水量。输出：地区名字
SELECT (SELECT name FROM t_area WHERE id = areaid) AS area_name, 
       sum(usenum) AS total_usenum
FROM t_account 
WHERE YEAR='2012'
GROUP BY areaid;

4. 分页查询

4.1 基于伪列的查询

在Oracle的表的使用过程中，实际表中还有一些附加的列，称为伪列(虚拟列)。

伪列就像表中的列一样，但是在表中并不存储。伪列是由ORACLE进行维护和管理的，用户只能查询，不能进行增删改操作。在SQL语句中想要获取伪列的值必须要显式指定伪列。

接下来学习两个伪列：ROWID和ROWNUM。

4.1.1 ROWID

表中的每一行在数据文件中都有一个物理地址，ROWID伪列返回的就是该行的物理地址。使用ROWID可以快速的定位表中的某一行。ROWID值可以唯一的标识表中的一行。由于ROWID返回的是该行的物理地址，因此使用ROWID可以显示行是如何存储的。

查询语句：

SELECT ROWID, t.* 
FROM t_area t 

查询结果如下：

可以通过指定ROWID来查询记录：

select rowid, t.* from t_area t where rowid='AAAM1uAAGAAAAD8AAC';

查询结果如下：

ROWID的组成：

AAAM1u （数据对象编号）数据对象指的就表
AAG（相对数据文件号）文件号就是指数据文件编号
AAAAD8（数据块号）
AAC（数据行号）

ROWID的使用场景：用来做去重复记录。

面试题

需求：请删除mydept表中的重复记录

在开发过程中，由于开发人员更换，会造数据存在大量的测试数据，测试数据会存在重复的问题

分析：

发现数据行号不同，早期插入的数据比较小

发现数据存在重复

delete from dept where not rowid in 
(
  select min(rowid) from dept group by dname,loc
)

4.1.2 ROWNUM

在查询的结果集中，rownum为结果集中每一行标识一个行号，第一行返回1，第二行返回2，以此类推。通过rownum伪列可以限制查询结果集中返回的行数。

查询语句：

SELECT rownum, ot.* 
FROM t_ownertype ot

查询结果如下：

Oracle数据库中的分页查询需要使用rownum伪列。

rownum在查询时的机制：在进行select查询时，每检索到一行记录，oracle就会给rownum添加一个行记录标识号(行号)。

4.2 分页查询

MySQL中的分页语句：SELECT * FROM T_OWNERS LIMIT 3 , 3 ;

4.2.1 简单分页

需求：分页查询账单表T_ACCOUNT，每页10条记录

分析1：我们在ORACLE进行分页查询，需要用到伪列ROWNUM 。首先显示前10条记录，语句如下：

SELECT rownum , ac.* 
FROM t_account ac 
WHERE rownum <= 10;

显示结果如下：

那么我们显示第11条到第20条的记录呢？编写语句：

SELECT rownum , ac.* 
FROM t_account ac 
WHERE rownum > 10 AND rownum <=20;

查询结果：

嗯？怎么没有结果？

ROWNUM产生过程： rownum是基于ORACLE在扫描表中每条记录时产生，表中的记录是逐行扫描，每扫描到一行，rownum就赋予一个行号。

因为rownum是在查询语句扫描每条记录时产生的，所以不能使用“大于”或“大于等于”符号，只能使用“小于”或“小于等于” 。（只用“等于”也不行）

那怎么办呢？

分析2：在ORACLE中进行分页查询，除了要用到伪列rownum，还需要嵌套查询（子查询）。

SELECT * 
FROM (SELECT rownum AS rm, ac.* FROM t_account ac WHERE rownum <=20)
WHERE rm >10; -- 注意：需要给子查询中的rownum起个别名

查询结果如下：

4.2.2 基于排序的分页

需求：分页查询账单表T_ACCOUNT，每页10条记录，按使用字数(USENUM)降序排序。

查询第2页数据，如果基于上边的语句添加排序，语句如下：

SELECT * 
FROM 
   (
    SELECT rownum AS rm, ac.* 
    FROM t_account ac 
    WHERE rownum <=20 ORDER BY usenum DESC
   )
WHERE rm >10;

查询结果如下：

经过验证，我们看到第2页的结果应该是下列记录：

所以推断刚才的语句是错误的！那为什么是错误的呢？

可以先单独执行嵌套查询里面的SQL语句：

SELECT rownum AS rm, ac.* 
FROM t_account ac 
WHERE rownum <=20 ORDER BY usenum DESC

查询结果如下：

发现排序后的rownum是乱的。这是因为rownum伪列的产生是在表记录扫描时产生的，而排序是后进行的，排序时rownum已经产生了，所以排序后rownum是乱的。

那该如何写呢？

很简单，只要再嵌套一层循环（一共三层），让结果先排序，然后对排序后的结果再产生rownum，这样就不会乱了。

SQL分页代码：

SELECT * 
FROM 
    (
      SELECT rownum AS rm, ac.* 
      FROM (SELECT * FROM t_account ORDER BY usenum desc) ac
      WHERE rownum <=20 
    )
WHERE rm >10;

执行结果：

后续在学习开窗函数后，可以对分页查询语句进行优化。

Oracle数据库中表连接, 子查询, 及分页查询

Data Retrieval Techniques: Table JOINs, Subqueries, and Pagination in Oracle Database

Oracle数据库中表连接, 子查询, 及分页查询

1. 多表关系

1.1 多表概念

1.2 多表关系：一对一

1.3 多表关系：一对多

1.4 多表关系：多对多

2. 多表查询

2.1 概述

2.2 内连接查询

2.4 左外连接查询

2.5 右外连接查询

2.5 全连接查询

2.5 自然连接查询

3. 子查询

3.1 概述

3.2 标量子查询

3.3 列子查询

3.4 行子查询

3.5 表子查询

4. 分页查询

4.1 基于伪列的查询

4.1.1 ROWID

面试题

4.1.2 ROWNUM

4.2 分页查询

4.2.1 简单分页

4.2.2 基于排序的分页

CATALOG

FEATURED TAGS

FRIENDS

Oracle数据库中 表连接, 子查询, 及分页查询

1. 多表关系

1.1 多表概念

1.2 多表关系：一对一

1.3 多表关系：一对多

1.4 多表关系：多对多

2. 多表查询

2.1 概述

2.2 内连接查询

2.4 左外连接查询

2.5 右外连接查询

2.5 全连接查询

2.5 自然连接查询

3. 子查询

3.1 概述

3.2 标量子查询

3.3 列子查询

3.4 行子查询

3.5 表子查询

4. 分页查询

4.1 基于伪列的查询

4.1.1 ROWID

面试题

4.1.2 ROWNUM

4.2 分页查询

4.2.1 简单分页

4.2.2 基于排序的分页

CATALOG

FEATURED TAGS

FRIENDS

Oracle数据库中表连接, 子查询, 及分页查询