子查询
不能一步求解的时候使用子查询
单行子查询
单行操作符
示例:查询工资比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;