oracle伪列子查询,Oracle 子查询和伪列

1、子查询

当一个查询是另一个查询的条件时，称之为子查询。

常见的子查询有单行子查询、多行子查询、多列子查询、做为from字句的子查询(内嵌视图查询)

1.1 、单行子查询

单行子查询是指查询结果为一行数据的子查询

实例  显示与SMITH同部门的所有员工

SELECT *

FROM emp

WHERE deptno = (SELECT deptno

FROM emp

WHERE ename = 'SMITH');

1.2、多行子查询

多行子查询指查询结果为多行数据的子查询

实例  查询和部门10的工作相同的雇员的名字、岗位、工资、部门号

SELECT ename, job, sal, deptno

FROM emp

WHERE job IN(SELECT DISTINCT job

FROM emp

WHERE deptno = 10);

1.2.1、 all关键字

实例  显示工资比部门30的所有员工的工资高的员工的姓名、工资和部门号

SELECT ename, sal, deptno

FROM emp

WHERE sal > ALL(SELECT sal

FROM emp

WHERE deptno = 30);

1.2.2、any关键字

实例 显示工资比部门30的任意一个员工的工资高的员工姓名、工资和部门号

SELECT ename, sal, deptno

FROM emp

WHERE sal > ANY (SELECT sal

FROM emp

WHERE deptno = 30);

1.3 多列子查询

多列子查询是指查询结果中包含多个字段的子查询

实例 查询与SMITH 的部门和岗位完全相同的所有雇员

SELECT *

FROM emp

WHERE(deptno, job) =(SELECT deptno, job

FROM emp

WHERE ename='SMITH');

1.4 内嵌视图查询查询

当在from子句中使用子查询时，该子查询会被作为一个视图来对待，因此叫做内嵌视图，当在from 子句中使用子查询时，必须给子查询指定别名(不能使用as关键字)

实例   显示高于自己部门平均工资的员工的信息

SELECT e.ename, e.sal, e.deptno,temp.avgsal

FROM emp e, (SELECT deptno, AVG(sal) avgsal FROM emp GROUP BY deptno) temp

WHERE e.deptno = temp.deptno AND e.sal >temp.avgsal;

2、伪列

伪列就像Oracle中的一个表列，但实际上它并未存储在表中。伪列可以从表中查询，但是不能插入、更新或删除它们的值。

常用的伪列：rowid和rownum。

2.1 、rowid

ROWID是一种数据类型，它使用基于64为编码的18个字符来唯一标识一条记录物理位置的一个ID，类似于Java中一个对象的哈希码，都是为了唯一标识对应对象的物理位置，需要注意的是ROWID虽然可以在表中进行查询，但是其值并未存储在表中，所以不支持增删改操作

实例一、 观察rowid 和 rownum

SELECT ROWNUM,ROWID,empno,ename,job

FROM emp

WHERE ROWNUM <= 5;

ROWID 的组成分析

数据对象编号

文件编号

块编号

行编号

OOOOOO

FFF

BBBBBB

RRR

可以用ROWID用来唯一标识表中数据(物理地址唯一)

应用 可以用来删除表中重复数据

DELETE FROM 表名 WHERE ROWID NOT IN( SELECT MIN(ROWID)

FROM表名GROUP BY DEPTNO);

2.2、rownum

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

ROWNUM与ROWID不同，ROWID是插入记录时生成，ROWNUM是查询数据时生成。ROWID标识的是行的物理地址。ROWNUM标识的是查询结果中的行的次序。

2.3、利用rownum实现分页查询

实例1、  查询前5名(0~5)员工的姓名，工作，工资(第一页)

SELECT ROWNUM rn,ename, job, sal

FROM emp

WHERE rn<=5;

实例2、查询5~10号员工的姓名，工作，工资(第二页)

SELECT * FROM (SELECT ROWNUM rn, ename，job, sal FROM emp)

WHERE rn > 5 AND rn <= 10;