SQL Fundamentals: 子查询 || WHERE,HAVING,FROM,SELECT子句中使用子查询，WITH子句
SQL Fundamentals || Oracle SQL语言
1、认识子查询
2、WHERE子句中使用子查询
3、在HAVING子句中使用子查询
4、在FROM子句中使用子查询
5、在SELECT子句中使用子查询
6、WITH子句
子查询（进阶）
8、行列转换
9、设置数据层次
一、认识子查询
- 子查询就是指的在一个完整的查询语句之中，嵌套若干个不同功能的小查询，从而一起完成复杂查询（复杂查询=限定查询+多表查询+统计查询+子查询）的一种编写形式，为了让读者更加清楚子查询的概念。
- 一个查询语句内部可以定义多个子查询；
- 子查询一般在WHERE、FROM、HAVING子句之中出现较多，也可以在SELECT子句中出现.
例子1、查询公司中工资最低的雇员的完整信息
分析：
1.查询最低工资：SELECT MIN(sal) FROM emp;
2.查询等于最低工资的雇员的信息：
SELECT * FROM emp
WHERE sal=(
SELECT MIN(sal) FROM emp;);
子查询可以返回的结果数据类型一共分为四种：
|
单行单列 |
返回的是一个具体列的内容，可以理解为一个单值数据； |
|
单行多列 |
返回一行数据中多个列的内容； |
|
多行单列 |
返回多行记录之中同一列的内容，相当于给出了一个操作范围； |
|
多行多列 |
查询返回的结果是一张临时表； |
子查询语法：
|
SELECT [DISTINCT] * | 分组字段1 [AS] [列别名] , [分组字段2 [AS] [列别名] , …],( SELECT [DISTINCT] * | 分组字段1 [AS] [列别名] , [分组字段2 [AS] [列别名] , …] FROM 表名称1 [表别名1] , 表名称2 [表别名2] …. [WHERE 条件(s)] [GROUP BY 分组字段1 , 分组字段2 , ….] [HAVING 过滤条件(s)] [ORDER BY 排序字段 ASC|DESC]) ... FROM 表名称1 [表别名1] , 表名称2 [表别名2] …, ( SELECT [DISTINCT] * | 分组字段1 [AS] [列别名] , [分组字段2 [AS] [列别名] , …] FROM 表名称1 [表别名1] , 表名称2 [表别名2] …. [WHERE 条件(s)] [GROUP BY 分组字段1 , 分组字段2 , ….] [HAVING 过滤条件(s)] [ORDER BY 排序字段 ASC|DESC]) [WHERE 条件(s) ... ( SELECT [DISTINCT] * | 分组字段1 [AS] [列别名] , [分组字段2 [AS] [列别名] , …] FROM 表名称1 [表别名1] , 表名称2 [表别名2] …. [WHERE 条件(s)] [GROUP BY 分组字段1 , 分组字段2 , ….] [HAVING 过滤条件(s)] [ORDER BY 排序字段 ASC|DESC])] [GROUP BY 分组字段1 , 分组字段2 , ….] [HAVING 过滤条件(s)...( SELECT [DISTINCT] * | 分组字段1 [AS] [列别名] , [分组字段2 [AS] [列别名] , …] FROM 表名称1 [表别名1] , 表名称2 [表别名2] …. [WHERE 条件(s)] [GROUP BY 分组字段1 , 分组字段2 , ….] [HAVING 过滤条件(s)] [ORDER BY 排序字段 ASC|DESC])] [ORDER BY 排序字段 ASC|DESC] ; |
子查询常见操作：
|
WHERE子句 |
此时子查询返回的结果一般都是 单行单列、单行多列、多行单列； |
|
HAVING子句 |
此时子查询返回的都是 单行单列数据，同时为了使用统计函数操作； |
|
FROM子句 |
此时子查询返回的结果一般都是 多行多列，可以按照一张数据表（临时表）的形式操作。 |
二、WHERE子句中使用子查询
- 可以在WHERE子句之中处理单行单列子查询、多行单列子查询、单行多列子查询。
- WHERE子句可以判断单个数值、多个数值；
- 使用IN、ANY、ALL可以处理多行单列子查询；
- 利用EXISTS()可以判断查询结果是否为null。
1、单行单列（子句中只有一行，一列）
|
例子1：查询出基本工资比ALLEN低的全部雇员信息 |
|
SELECT * FROM emp WHERE sal< ( SELECT sal FROM emp WHERE ename='ALLEN') ; |
|
例子2：查询基本工资高于公司平均薪金的全部雇员信息 |
|
SELECT * FROM emp WHERE sal>( SELECT AVG(sal) FROM emp) ; |
|
例子3：查找出与ALLEN从事同一工作，并且基本工资高于雇员编号为7521的全部雇员信息 |
|
SELECT * FROM emp WHERE job=(SELECT job FROM emp WHERE ename='ALLEN') AND sal>(SELECT sal FROM emp WHERE empno=7521) ; |
2、单行多列（子句中有一行SCOTT，多列sal和job）
|
例子1：查询与SCOTT从事同一工作且工资相同的雇员信息 |
|
SELECT * FROM emp WHERE (job,sal)=( SELECT job,sal FROM emp WHERE ename='SCOTT') AND ename<>'SCOTT' ; |
|
例子2：查询与雇员7566从事同一工作且领导相同的全部雇员信息 |
|
SELECT * FROM emp WHERE ( job,mgr)=( SELECT job,mgr FROM emp WHERE empno=7566) AND empno<>7566 ; |
|
例子3：查询与ALLEN从事同一工作且在同一年雇佣的全部雇员信息（包括ALLEN） |
|
SELECT * FROM emp WHERE (job, TO_CHAR(hiredate, 'yyyy'))=( SELECT job, TO_CHAR(hiredate, 'yyyy') FROM emp WHERE ename='ALLEN') ; |
3、多行单列
在使用多行子查询时，主要使用三种操作符：IN、ANY、ALL
IN和NOT IN
- 如果在IN中子查询返回的数值有null，那么不会有影响，如果NOT IN中子查询返回数据有null，那么就表示不会有任何数据返回
|
例子1：查询出与每个部门中最低工资相同的全部雇员信息 |
|
SELECT * FROM emp WHERE sal IN ( SELECT MIN(sal) FROM emp GROUP BY deptno) ; |
|
例子2：查询出不与每个部门中最低工资相同的全部雇员信息 |
|
SELECT * FROM emp WHERE sal NOT IN ( SELECT MIN(sal) FROM emp GROUP BY deptno) ; |
ANY（SOME）
|
=ANY 表示与子查询中的每个元素进行比较，功能与IN类似（然而<>ANY不等价于NOT IN）； |
SELECT * FROM emp WHERE sal=ANY ( SELECT MIN(sal) FROM emp WHERE job='MANAGER' GROUP BY deptno) ; |
|
>ANY 比子查询中返回结果的最小的要大（还包含了>=ANY）； |
SELECT * FROM emp WHERE sal >ANY ( SELECT MIN(sal) FROM emp WHERE job='MANAGER' GROUP BY deptno) ; |
|
<ANY 比子查询中返回结果的最大的要小（还包含了<=ANY） |
SELECT * FROM emp WHERE sal <ANY ( SELECT MIN(sal) FROM emp WHERE job='MANAGER' GROUP BY deptno) ; |
ALL操作符
|
<>ALL 等价于NOT IN（但是=ALL并不等价于IN）； |
|
使用<>ALL操作符完成查询 SELECT * FROM emp WHERE sal <>ALL ( SELECT MIN(sal) FROM emp WHERE job='MANAGER' GROUP BY deptno) ; |
|
>ALL 比子查询中最大的值还要大（还包含了>=ALL）； |
|
使用>ALL操作符完成查询 SELECT * FROM emp WHERE sal >ALL ( SELECT MIN(sal) FROM emp WHERE job='MANAGER' GROUP BY deptno) ; |
|
<ALL 比子查询中最小的值还要小（还包含了<=ALL）。 |
|
使用<ALL操作符完成查询 SELECT * FROM emp WHERE sal <ALL ( SELECT MIN(sal) FROM emp WHERE job='MANAGER' GROUP BY deptno) ; |
空值数据判断
在SQL之中提供了一个exists结构用于判断子查询是否有数据返回。如果子查询中有数据返回，则exists结构返回true，反之返回false。
|
验证exists结构 |
|
SELECT * FROM emp WHERE EXISTS( SELECT * FROM emp WHERE empno=9999) ; |
|
验证exists结构 |
|
SELECT * FROM emp WHERE EXISTS( SELECT * FROM emp) ; |
|
使用NOT EXISTS |
|
SELECT * FROM emp WHERE NOT EXISTS( (SELECT * FROM emp WHERE empno=9999)); |
三、在HAVING子句中使用子查询
在HAVING子句中使用子查询，子查询返回的都是单行单列数据，同时也可以在HAVING中利用统计函数进行判断。
|
例子1：查询部门编号、雇员人数、平均工资，并且要求这些部门的平均工资高于公司平均薪金 |
|
SELECT deptno, COUNT(empno), AVG(sal) FROM emp GROUP BY deptno HAVING AVG(sal)>( SELECT AVG(sal) FROM emp); |
|
例子2：查询出每个部门平均工资最高的部门名称及平均工资 |
|
SELECT d.dname, ROUND(AVG(e.sal),2) FROM emp e ,dept d WHERE e.deptno=d.deptno GROUP BY d.dname HAVING AVG(sal)=( SELECT MAX(AVG(sal)) FROM emp GROUP BY deptno) ; |
四、在FROM子句中使用子查询
- 如果现在子查询返回的数据是多行多列的，那么就可以将其当做一张数据表（同时存在多行多列）来使用，并且这种子查询一般都出现在FROM子句之中。
- FROM子句出现的子查询返回结构为多行多列；
- 利用子查询可以解决多表查询所带来的性能问题。
|
例子1：要求查询出每个部门的编号、名称、位置、部门人数、平均工资 使用子查询能解决多表查询所带来的性能问题 |
|
多字段分组实现方式（会产生笛卡尔积）： SELECT d.deptno, d.dname, d.loc, COUNT(e.empno),AVG(e.sal) FROM emp e, dept d WHERE e.deptno(+)=d.deptno GROUP BY d.deptno,d.dname,d.loc 子查询实现方式： SELECT d.deptno,d.dname,d.loc,temp.count,temp.avg FROM dept d, (SELECT deptno dno, COUNT(empno) count , ROUND(AVG(sal),2) avg FROM emp GROUP BY deptno) temp WHERE d.deptno=temp.dno(+) ; |
|
例子2：查询出所有在部门“SALES”（销售部）工作的员工的编号、姓名、基本工资、奖金、职位、雇佣日期、部门的最高和最低工资。 |
|
确定所需要的数据表： dept表：销售部 emp表：员工信息 emp表：统计最高最低工资 没有关联字段，也不需要关联字段 |
|
步骤一：查询销售部的部门编号 SELECT deptno FROM dept WHERE dname='SALES'; 步骤二：此部门的雇员信息 SELECT empno,ename,sal,comm,job,hiredate FROM emp WHERE deptno=( SELECT deptno FROM dept WHERE dname='SALES' ); 步骤三：最高和最低工资，使用MAX()和MIN()函数 因为统计函数有限制，要么单独使用，要么结合GROUP BY函数，统计函数嵌套时不允许出现任何字段。 目前在整个SELECT查询里面需要统计查询，但无法直接使用统计函数，可以在子查询中完成，而且这个子查询一定返回一个多行多列，在FROM子句中出现 SELECT e.empno, e.ename, e.sal, e.comm, e.job, e.hiredate, temp.max, temp.min FROM emp e, ( SELECT deptno dno,MAX(sal) max,MIN(sal) min FROM emp GROUP BY deptno ) temp è 子查询负责统计信息，使用temp表示临时表的统计结果 WHERE deptno=( SELECT deptno FROM dept WHERE dname='SALES' AND e.deptno=temp.dno ); |
|
例子3：查询出所有薪金高于公司平均薪金的员工编号、姓名、基本工资、职位、雇佣日期，所在部门名称、位置，上级领导姓名，公司的工资等级，部门人数、平均工资、平均服务年限。 |
|
分析： 确定所需要的数据表 emp表：员工编号，姓名，工资，职位，雇佣日期 dept表：部门信息 emp表：自身关联上级领导姓名 salgrade表：工资等级 emp表：统计人数，平均工资，平均服务年限 确定已知的关联字段 雇员和部门：emp.deptno=dept.deptno 雇员和领导：emp.mgr=memp.empno 雇员和工资等级：emp.sal BETWEEN salgrade.losal AND salgrade.hisal |
|
步骤一：AVG()函数统计公司平均薪金 SELECT avg(sal) FROM emp; 结果是单行单列，只能在WHERE和HAVING子句中出现，这里没有分组，只能在WHERE中出现 步骤二：查询高于平均薪金的员工编号，姓名，基本工资等 SELECT e.empno, e.ename, e.sal, e.job, e.hiredate FROM emp e WHERE e.sal >(SELECT avg(sal) FROM emp); 步骤三：和dept表、salgrade表和emp表领导信息进行关联(多表关联使用关联字段消除笛卡尔积，由于有的员工没有领导，考虑外连接) SELECT e.empno, e.ename, e.sal, e.job, e.hiredate ,d.dname, d.loc, m.ename mname, s.grade FROM emp e, dept d, emp m, salgrade s WHERE e.sal >(SELECT avg(sal) FROM emp) AND e.deptno=d.deptno AND e.mgr=m.empno(+) AND e.sal BETWEEN s.losal AND s.hisal; 步骤四：部门人数，平均工资、平均服务年限，需要使用函数AVG()，这里使用子查询 SELECT e.empno, e.ename, e.sal, e.job, e.hiredate ,d.dname, d.loc, m.ename mname, s.grade ,temp.count,temp.avgsal, temp.avgyear FROM emp e, dept d, emp m, salgrade s,( SELECT deptno dno ,COUNT(empno) count,ROUND(AVG(sal),2) avgsal,ROUND(AVG(MONTHS_BETWEEN(SYSDATE,hiredate)/12),2) avgyear FROM emp GROUP BY deptno ) temp WHERE e.sal > (SELECT avg(sal) FROM emp) AND e.deptno=d.deptno AND e.mgr=m.empno(+) AND e.sal BETWEEN s.losal AND s.hisal AND e.deptno=temp.dno; |
|
例子4：列出薪金比“ALLEN”或“CLARK”多的所有员工的编号、姓名、基本工资、部门名称、其领导姓名，部门人数。 |
|
分析： 确定所需要的数据表 emp表：员工编号，姓名，工资，职位，雇佣日期 dept表：部门信息 emp表：自身关联上级领导姓名 emp表：统计部门人数 确定已知的关联字段 雇员和部门：emp.deptno=dept.deptno 雇员和领导：emp.mgr=memp.empno |
|
步骤一：查询"ALLEN”或“CLARK”的薪金 SELECT e.ename,e.sal FROM emp e WHERE e.ename IN('JONES','CLARK'); (在WHERE子句中这里返回的是多行单列，这里使用IN\ANY\ALL) 步骤二：查询薪金比“ALLEN”或“CLARK”多的所有员工,加上dept表查询部门以及emp表查询领导信息 SELECT e.ename,e.sal,e.empno,d.dname, m.ename mname FROM emp e ,dept d, emp m WHERE e.sal > ANY( SELECT sal FROM emp WHERE ename IN('JONES','CLARK') ) AND e.deptno=d.deptno AND e.mgr=m.empno(+); 步骤三：查询部门人数，需要使用COUNT()函数 SELECT deptno dno,COUNT(empno) count FROM emp GROUP BY deptno 这里要注意，统计函数要么单独使用，要和分组字段使用就必须使用GROUP BY，否则无法使用的. SELECT e.ename,e.sal,e.empno,d.dname, m.ename mname,temp.count FROM emp e ,dept d, emp m,(SELECT deptno dno,COUNT(empno) count FROM emp GROUP BY deptno) temp WHERE e.sal > ANY( SELECT sal FROM emp WHERE ename IN('JONES','CLARK') ) AND e.deptno=d.deptno AND e.mgr=m.empno(+) AND e.deptno=temp.dno; |
|
例子5：列出公司各个部门的经理（假设每个部门只有一个经理，job为“MANAGER”）的姓名、薪金、部门名称、部门人数、部门平均工资。 |
|
分析： 确定所需要的数据表 emp表：员工编号，姓名，工资，经理 dept表：部门信息 emp表：统计部门人数，平均工资 确定已知的关联字段 雇员（经理）和部门：emp.deptno=dept.deptno |
|
步骤一：列出经理的姓名，薪资，部门名称 SELECT e.ename, e.sal, d.dname FROM emp e, dept d WHERE e.job='MANAGER' AND e.deptno=d.deptno; 步骤二：统计部门人数，平均工资 SELECT e.ename, e.sal, d.dname,temp.count, temp.avg FROM emp e, dept d,( SELECT deptno dno,COUNT(empno) count,ROUND(AVG(sal),2) avg FROM emp GROUP BY deptno ) temp WHERE e.job='MANAGER' AND e.deptno=d.deptno AND e.deptno=temp.dno; |
五、在SELECT子句中使用子查询
|
例子1：查询出公司每个部门的编号、名称、位置、部门人数、平均工资 |
|
分析： 确定所需要的表： dept表：部门信息 emp表：平均工资，部门人数 |
|
步骤一：查询部门信息 SELECT deptno,dname,loc FROM dept d; 步骤二：查询部门人数 SELECT COUNT(empno) FROM emp WHERE deptno=dept.deptno GROUP BY deptno 步骤三：查询平均工资 SELECT AVG(sal) FROM emp WHERE deptno=dept.deptno GROUP BY deptno 步骤四：将平均工资和部门人数放在SELECT子句的字段中 SELECT deptno,dname,loc, ( SELECT COUNT(empno) FROM emp WHERE deptno=d.deptno GROUP BY deptno) count, (SELECT AVG(sal) FROM emp WHERE deptno=d.deptno GROUP BY deptno) sal FROM dept d; |
六、WITH子句
- 临时表是一个查询结果，查询结果返回的是多上多列，那么可以将其定义在FROM子句之中，表示其为一张临时表.
- 除了在FROM子句之中出现临时表之外，也可以利用WITH子句直接定义临时表.
- 可以使用WITH子句创建临时查询表
- WITH子句可以构建一张临时表供查询使用。
- WITH子句提供了一种定义临时表的操作方法，如果在一个查询之中，要反复使用到一些数据，那么就可以将这些数据定义在WITH子句之中。
|
使用WITH子句将emp表中的数据定义为临时表 |
|
WITH e AS ( SELECT * FROM emp) SELECT * FROM e ;
|
|
查询每个部门的编号、名称、位置、部门平均工资、人数 |
|
WITH e AS ( SELECT deptno dno , ROUND(AVG(sal),2) avg , COUNT(sal) count FROM emp GROUP BY deptno)
SELECT d.deptno,d.dname,d.loc,e.count,e.avg FROM e , dept d WHERE e.dno(+)=d.deptno ; |
|
查询每个部门工资最高的雇员编号、姓名、职位、雇佣日期、工资、部门编号、部门名称，显示的结果按照部门编号进行排序 |
|
WITH e AS ( SELECT deptno dno , MAX(sal) max FROM emp GROUP BY deptno)
SELECT em.empno , em.ename , em.job , em.hiredate , em.sal , d.deptno,d.dname FROM e , emp em , dept d WHERE e.dno=em.deptno AND em.sal=e.max AND e.dno=d.deptno ORDER BY em.deptno ; |
|
FROM子句中的例子：例子5：列出公司各个部门的经理（假设每个部门只有一个经理，job为“MANAGER”）的姓名、薪金、部门名称、部门人数、部门平均工资。 |
|
WITH t AS( SELECT deptno dno,COUNT(empno) count,ROUND(AVG(sal),2) avg FROM emp GROUP BY deptno) SELECT e.ename, e.sal, d.dname,t.count, t.avg FROM emp e, dept d,t WHERE e.job='MANAGER' AND e.deptno=d.deptno AND e.deptno=t.dno; |
