Oracle多表的复杂查询

一．分页查询： 1. 根据rowid来分 select * from t_xiaoxi where rowid in (select rid from (select rownum rn, rid fr

一．分页查询：

1. 根据rowid来分
select * from t_xiaoxi where rowid in (select rid from (select rownum rn, rid from(select rowid rid, cid from t_xiaoxi order by cid desc) where rownum<10000) where rn>9980) order by cid desc;
执行时间0.03秒

2. 按分析函数来分
select * from (select t.*, row_number() over(order by cid desc) rk from t_xiaoxi t) where rk<10000 and rk>9980;
执行时间1.01秒

3. 按rownum来分
select * from (select t.*,rownum rn from(select * from t_xiaoxi order by cid desc)t where rownum<10000) where rn>9980;

执行时间0.1秒

按rownum来分
a. rownum 分页
SELECT * FROM emp;
b. 显示rownum[Oracle分配的]
SELECT e.*, ROWNUM rn FROM (SELECT * FROM emp) e;
rn相当于Oracle分配的行的ID号
c.挑选出6—10条记录
先查出1-10条记录
SELECT e.*, ROWNUM rn FROM (SELECT * FROM emp) e WHERE ROWNUM <= 10;
如果后面加上rownum>=6是不行的，
然后查出6-10条记录
SELECT * FROM (SELECT e.*, ROWNUM rn FROM (SELECT * FROM emp) e WHERE ROWNUM <= 10) WHERE rn >= 6;
*指定查询列，只需要修改最里层的子查询

SELECT * FROM (SELECT e.*, ROWNUM rn FROM (SELECT ename, sal FROM emp) e WHERE ROWNUM <= 10) WHERE rn >= 6;

*排序查询，，只需要修改最里层的子查询
工资排序后查询6-10条数据
SELECT * FROM (SELECT e.*, ROWNUM rn FROM (SELECT ename, sal FROM emp ORDER by sal) e WHERE ROWNUM <= 10) WHERE rn >= 6;

二．用查询结果创建新表
CREATE TABLE mytable (id, name, sal, job, deptno) as SELECT empno, ename, sal, job, deptno FROM emp;

三．合并查询

实际应用中，有时为了合并多个select语句的结果，可以使用集合操作符号union，union all，intersect，minus
多用于数据量比较大的数据局库，运行速度快。
1). union
该操作符用于取得两个结果集的并集。当使用该操作符时，会自动去掉结果集中重复行。
SELECT ename, sal, job FROM emp WHERE sal >2500
UNION
SELECT ename, sal, job FROM emp WHERE job = 'MANAGER';
2).union all
该操作符与union相似，但是它不会取消重复行，而且不会排序。
SELECT ename, sal, job FROM emp WHERE sal >2500
UNION ALL
SELECT ename, sal, job FROM emp WHERE job = 'MANAGER';
该操作符用于取得两个结果集的并集。当使用该操作符时，会自动去掉结果集中重复行。
3). intersect
使用该操作符用于取得两个结果集的交集。
SELECT ename, sal, job FROM emp WHERE sal >2500
INTERSECT
SELECT ename, sal, job FROM emp WHERE job = 'MANAGER';
4). minus
使用改操作符用于取得两个结果集的差集，他只会显示存在第一个集合中，而不存在第二个集合中的数据。
SELECT ename, sal, job FROM emp WHERE sal >2500
MINUS
SELECT ename, sal, job FROM emp WHERE job = 'MANAGER';
（MINUS就是减法的意思）

四．左右连接
1、内连接(自然连接): 只有两个表相匹配的行才能在结果集中出现
2、外连接: 包括
（1）左外连接 (左边的表不加限制) select a.studentno, a.studentname, b.classname
from students a, classes b where a.classid = b.classid(+);
（2）右外连接(右边的表不加限制) select a.studentno, a.studentname, b.classname
from students a, classes b where a.classid(+) = b.classid
（3）全外连接(左右两表都不加限制)

总之,
左连接显示左边全部的和右边与左边相同的
右连接显示右边全部的和左边与右边相同的
内连接是只显示满足条件的!
补充：
使用（+）的注意事项：
1.（+）操作符只能出现在where子句中，并且不能与outer join语法同时使用。
2.当使用（+）操作符执行外连接时，如果在where子句中包含有多个条件，则必须在所有条件中都包含（+）操作符。
3.（+）操作符不能与or和in操作符一起使用。
4.（+）操作符只能用于实现左外连接和右外连接，而不能用于实现完全外连接。

相关阅读：

Oracle 并行查询

Oracle用户信息查询操作语句

Oracle单表查询某列最大最小值的性能问题

回收站引发Oracle查询表空间使用缓慢