5 子查询

子查询就是将一个查询（子查询）的结果作为另一个查询（主查询）的数据来源或判断条件的查询。常见的子查询有WHERE子查询、HAVING子查询、FROM子查询、SELECT子查询、EXISTS子查询，子查询要使用小括号()；

1、WHERE子查询

在WHERE子句中进行使用查询

--查询薪资比平均薪资低的员工信息

SELECT *
FROM epm
WHERE sal<(SELECT AVG(sal) FROM emp);

 

2、HAVING子查询

HAVING子句是对分组统计函数进行过滤的子句，也可以在HAVING子句中使用子查询

--查询平均薪资最高的职位及其平均工资

SELECT job,avg(sal)
FROM emp
GROUP BY job
HAVING avg(sal)=(SELECT MAX(avg(sal)) FROM emp GROUP BY job);

 

3、FROM子查询

FROM子查询就是将一个查询结构（一般是多行多列）作为主查询的数据源

--查询平均薪资高于2000的职位以及该职位的平均薪资

SELECT job,AVG(sal)
FROM 
(SELECT job,AVG(sal) AS AVGSAL FROM emp GROUP BY job) AS temp
WHERE tmp.AVGSAL>2000;

 

4、SELECT子查询

SELECT子查询在SELECT子句中使用查询的结果（一般会和dual空表一起使用，dual是一个虚拟表）

--职位是SALESMAN的员工占总员工的比例

SELECT
(SELECT COUNT(*) FROM emp WHERE job='SALESMAN')/(SELECT COUNT(*) FROM emp)
FROM DUAL;

 

5、EXISTS子查询

将主查询的数据带到子查询中验证，如果成功则返回TRUE，否则FALSE。主查询接收TRUE是就会显示这条数据，FALSE就不会显示。EXISTS用于检查子查询是否至少会返回一行数据，该子查询实际上并不返回任何数据，而是返回值TRUE或者FALSE。

--查询有部门的员工信息

SELECT *
FROM emp AS e
WHERE EXISTS(
SELECT *
FROM dept AS e
WHERE e.deptno=d.deptno);

--查询薪资排名第n个员工的信息（包括并列排名）

--思路：
--1.先按薪资降薪分组
--2.再取前n名薪资中最低的薪资，即第n名的薪资
--3.最后在原表中找出薪资与最低薪资相同的员工信息


SELECT *
FROM emp
WHERE sal=(SELECT MIN(sal)
FROM
(SELECT ROWNUM,sal
FROM (SELECT sal FROM emp GROUP BY sal ORDER BY sal desc)
WHERE ROWNUM<=n));