查询的嵌套使用

查询的嵌套:
一个查询中使用另一个查询
注意:查询中嵌套的查询,成为子查询,需要使用()括起来
按照嵌套的位置:
WHERE嵌套子查询
SELECT
FROM
WHERE 比较中的条件没有直接给出,套用查询得到;

eg1:查询薪资高于Elena(first_name)的员工信息
SELECT salary
FROM s_emp
WHERE first_name='Elena';

SELECT first_name,salary
FROM s_emp
WHERE salary>?;
组合:
SELECT first_name,salary
FROM s_emp
WHERE salary>(
SELECT salary
FROM s_emp
WHERE first_name='Elena'

);

eg2:查询大于公司平均薪资的员工名字,薪资
SELECT first_name,salary
FROM s_emp
WHERE salary>(
SELECT AVG(salary)
FROM s_emp);

eg3:查询高于  41号部门平均薪资  的员工名,薪资
SELECT first_name,salary
FROM s_emp
WHERE salary>(
SELECT AVG(salary)
FROM s_emp
WHERE dept_id=41
);

eg4:查询Elena所在部门的平均薪资
SELECT AVG(salary)
FROM s_emp
WHERE dept_id=(
SELECT dept_id
FROM s_emp
WHERE first_name='Elena'
);

eg5:查询1号区域所有部门的平均薪资

区域编号->部门编号->对应的员工

SELECT id
FROM s_dept
WHERE region_id=1;

SELECT AVG(salary)
FROM s_emp
WHERE dept_id IN (
SELECT id
FROM s_dept
WHERE region_id=1
);

注意:
子查询返回单行,单行子查询,可以使用=,>,<等进行比较
子查询返回多行,多行子查询,不能直接使用=,>,<等
表示在这些之内,in
表示比这些任意一个值都大,可以大于max(), 
表示大于其中一个即可,    可以大于min(), 
表示比这些任意一个值都小,可以小于min(), 

表示小于其中一个即可,可以小于max(), 

HAVING嵌套子查询
同WHERE
对组筛选时,条件没有直接给,可以通过子查询得到想要的结果

eg6:查询高于 44号部门人数  的部门编号,部门人数
1)查询出44号部门的人数
2)查询出部门人数大于1)结果的部门

SELECT COUNT(id)
FROM s_emp
WHERE dept_id=44;

SELECT dept_id,COUNT(id)
FROM s_emp
GROUP BY dept_id
HAVING COUNT(id)>(
SELECT COUNT(id)
FROM s_emp
WHERE dept_id=44
);

eg7:查询 部门平均薪资  高于  公司平均薪资  的部门编号,平均薪资
SELECT AVG(salary)

FROM s_emp;

SELECT dept_id,AVG(salary)
FROM s_emp
GROUP BY dept_id
HAVING AVG(salary)>(
SELECT AVG(salary)
FROM s_emp

);

eg8:查询 部门所有人薪资  都高于  公司平均薪资  的部门编号
SELECT dept_id
FROM s_emp
GROUP BY dept_id
HAVING MIN(salary)>(
SELECT AVG(salary)
FROM s_emp
);

FROM中嵌套子查询
查询返回的是结果集:行,列组成,可以基于一个查询结果再做查询
把查询结果当做表

eg9:查询出每个部门编号,部门名,平均薪资
SELECT d.id,d.name,AVG(e.salary)
FROM s_emp e,s_dept d
WHERE e.dept_id=d.id
GROUP BY d.id,d.name;

--获取每个部门编号,部门平均薪资
SELECT dept_id,AVG(salary) avg_sal
FROM s_emp
GROUP BY dept_id;

--基于上面这个结果,再次和部门表联查,得到部门名
SELECT d.id,d.name,t.avg_sal
FROM (
SELECT dept_id,AVG(salary) avg_sal
FROM s_emp
GROUP BY dept_id
) t,s_dept d

WHERE t.dept_id=d.id;

eg9:查询员工表中第4到6条记录
SELECT *
FROM(
SELECT s_emp.*,rownum rn
FROM s_emp
WHERE rownum<=6
)
WHERE rn>=4;

eg10:查询出薪资最高的5位员工的信息,重要,经典
SELECT *
FROM (
SELECT id,first_name,salary
FROM s_emp
ORDER BY salary DESC
) WHERE rownum<=5;
--对排序后的结果集再次做查询,取前五个