Oracle——子查询

子查询

不能一步求解的时候使用子查询

单行子查询

单行操作符

示例：查询工资比SCOTT高的员工信息

SELECT
	* 
FROM
	emp 
WHERE
	sal > ( SELECT sal FROM emp WHERE ename = 'SCOTT' );

注意点

1、括号

2、合理的书写风格

3、可以在主查询的where select having from 后面使用子查询

• where：

SELECT
	* 
FROM
	emp 
WHERE
	sal > ( SELECT sal FROM emp WHERE ename = 'SCOTT' );

• select：

SELECT
	empno,
	ename,
	sal,
	( SELECT job FROM emp WHERE empno = 7839 ) 第四列 
FROM
	emp;

• having

SELECT
	DEPTNO,
	MIN( SAL ) 
FROM
	EMP 
GROUP BY
	DEPTNO 
HAVING
	MAX( SAL ) > (SELECT SAL FROM emp WHERE EMPNO=7369);

4、不可以在group by使用子查询

5、强调from后面的子查询

SELECT
	* 
FROM
	( SELECT empno, ename, sal FROM emp );	-- 强调from后面的子查询

6、主查询和子查询可以不是同一张表，只要子查询返回的结果主查询可以使用即可

7、一般不在子查询中排序；但在top-n分析问题中 必须对子查询排序

• top-n问题：

8、一般先执行子查询，再执行主查询；但相关子查询例外

• 相关子查询问题：将主查询中的值作为参数传递给子查询

9、单行子查询只能使用单行操作符；多行子查询只能使用多行操作符

10、子查询中的null：如果子查询中有null，要将其排除（not in语法中）

SQL优化3：多表查询和子查询之间，尽量使用多表查询

多行子查询

多行操作符

示例1：查询工资比30号部门任意一个员工高的员工信息

SELECT
	* 
FROM
	emp 
WHERE
	sal > ANY ( SELECT sal FROM emp WHERE deptno = 30 );

示例2：查询工资比30号部门所有员工高的员工信息

SELECT
	* 
FROM
	emp 
WHERE
	sal > ALL ( SELECT sal FROM emp WHERE deptno = 30 );

练习

/*
rownum	行号
1、rownum永远按照默认的顺序生成
2、rownum只能使用< <=；不能使用> >=
*/

-- 1、rownum永远按照默认的顺序生成

SELECT ROWNUM,
	empno,
	ename,
	sal 
FROM
	( SELECT * FROM emp ORDER BY sal DESC ) 
WHERE
	ROWNUM <= 3;

-- 2、rownum只能使用< <=；不能使用> >=
-- Oracle分页查询，取5-8之间的数据

SELECT
	* 
FROM
	( SELECT ROWNUM r, e1.* FROM ( SELECT * FROM emp ORDER BY sal ) e1 WHERE ROWNUM <= 8 ) 
WHERE
	r >= 5;

-- 思路：将emp表和group by之后的表进行连接

select 
	empno,
    ename,
    sal,
	avgsal
from
	emp e,
	(select avg(sal) avgsal,deptno from emp group by deptno) g 
where 
	e.deptno=g.deptno and e.sal>avgsal;
	
-- 相关子查询版：将e传递给子查询
	
select 
	empno,
    ename,
    sal,
	(select avg(sal) from emp where deptno = e.deptno) avgsal	-- 一开始实际上没查这行，等到子查询查完之后采查的这行
from
	emp e
where 
	e.sal > (select avg(sal) from emp where deptno = e.deptno)

select count(*) Total,
	 sum(decode(to_char(hiredate,'yyyy'),'1980',1,0)) "1980",
	 sum(decode(to_char(hiredate,'yyyy'),'1981',1,0)) "1981",
	 sum(decode(to_char(hiredate,'yyyy'),'1982',1,0)) "1982",
	 sum(decode(to_char(hiredate,'yyyy'),'1987',1,0)) "1987"
from emp;