Oracle | 子查询和伪列

1. 子查询

（1）单行子查询

（2）多行子查询

         1）ANY子查询

         2）ALL 子查询

2. 伪列 （ROWID 和 ROWNUM）

---

1. 子查询

子查询根据 返回值的记录多少 分为 单行子查询 和 多行子查询

（1）单行子查询：不向外部返回结果，或者只返回一行结果，可以使用 =、>、<、>=、<=、<>比较符

（2）多行子查询：向外部返回零行、一行或者多行结果，可以使用 IN和 NOT IN，<ANY，>ANY，<ALL，>ALL 比较符

子查询的内容可以放在FROM后面、WHERE后面、HAVING后面等

子查询的内容必须用小括号来界定

（1）单行子查询

查询出销售部（SALES）下面的员工姓名，工作，工资

SELECT ENAME,JOB,SAL FROM EMP

WHERE DEPTNO = (SELECT DEPTNO FROM DEPT WHERE DNAME='SALES')

（2）多行子查询

 1）ANY子查询

查询出Emp表中比任意一个销售员(“SALESMAN”)工资低的员工姓名、工作、工资

SELECT ENAME,JOB,SAL FROM EMP

WHERE SAL<ANY (SELECT SAL FROM EMP WHERE JOB='SALESMAN')

 2）ALL 子查询

查询出比所有销售员的工资都高的员工姓名，工作，工资

SELECT ENAME,JOB,SAL FROM EMP

WHERE SAL >ALL (SELECT SAL FROM EMP WHERE JOB='SALESMAN')

2. 伪列 （ROWID 和 ROWNUM）

（1）ROWID 伪列

表中的每一行在数据文件中都有一个物理地址，ROWID伪列返回该行的物理地址

SELECT ROWID, NAME

FROM USER_INFO

WHERE NAME Like '林_' ;

（2）ROWNUM 伪列

ROWNUM 为结果集的行号，第一行返回1，第二行返回2，以此类推

通过 ROWNUM 伪列可以限制查询结果集中返回的行数

1）显示 ROWNUM 行号

SELECT ROWID,ROWNUM,NAME

FROM USER_INFO

WHERE NAME Like '林_'  AND ROWNUM<10;

注：ROWNUM<10 返回前9条记录

2） ROWNUM 取前多少条记录

查询出工资最高的前5名员工的姓名、工资和工资

“工资最高的前5名” 需要先降序排序，再取前5名；对排序的结果重新做二次查询，产生新的ROWNUM才能作为查询的条件依据

SELECT ROWNUM, T.*  FROM

(SELECT ENAME,JOB,SAL

FROM EMP ORDER BY SAL DESC) T

WHERE ROWNUM<=5

3）ROWNUM 分页

查询出表 EMP 中第5条到第10条之间的记录

SELECT  *  FROM

(SELECT ROWNUM R,ENAME,JOB,SAL

FROM EMP WHERE ROWNUM<=10)

WHERE R>5

注：内部查询中得到 ROWNUM 并且用别名 R 记录，供外层条件使用

       使用的 R 是内层产生的 ROWNUM，在外层看来，内层查询的 ROWNUM 是正常的一列

注：ROWNUM 与 ROWID 不同

ROWID 是插入记录时生成，ROWNUM 是查询数据时生成

ROWID 标识的是行的物理地址，ROWNUM 标识的是查询结果中行的次序