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

1. 子查询

(1)单行子查询

(2)多行子查询

1)ANY子查询

2)ALL 子查询

2. 伪列 (ROWID 和 ROWNUM)

1. 子查询

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

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

(2)多行子查询：向外部返回零行、一行或者多行结果，可以使用 IN和 NOT IN，ANY，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

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

SELECT ENAME,JOB,SAL FROM EMP

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

2. 伪列 (ROWID 和 ROWNUM)

(1)ROWID 伪列

表中的每一行在数据文件中都有一个物理地址，ROWID伪列返回该行的物理地址SELECTROWID, 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 标识的是查询结果中行的次序