1条件子查询

一个查询结果，作为另一个查询的过滤条件
select ... where a=(select ...);

单值子查询(单行)

= > >= < <=

例子：求工资小于平均工资员工

>select id,fname,sal
    -> from emps
    -> where sal<avg(sal);
ERROR 1111 (HY000): Invalid use of group function

-- 多行函数不能写在where后，要这样写

select id,fname,sal
from emps
where sal<(select avg(sal) from emps);

多值子查询(多行)

in 
> all (select ...) -- 比最大值大
> any -- 比最小值大

-- 只有一个人的部门，查询这些员工
-- 1.按部门分组求人数，过滤只有一人的部门
select dept_id
from emps
where dept_id is not null
group by dept_id
having count(*)=1;
-- 2.用部门id过滤查询员工
select id,fname,sal,dept_id
from emps
where dept_id in(
select dept_id
from emps
where dept_id is not null
group by dept_id
having count(*)=1
);

多列子查询(多列)

where (a,b) in (select ...)
where (a,b) = (select ...)

求每个部门,拿最高工资的员工

-- 先求每个部门的最高工资值
select dept_id,max(sal) as maxsal
from emps
where dept_id is not null
group by dept_id;
-- 求每个部门,拿最高工资的员工
select id,fname,sal,dept_id
from emps
where (dept_id,sal) in
(select dept_id,max(sal) as maxsal
from emps
where dept_id is not null
group by dept_id);

select id,fname,sal,dept_id
from emps
where (dept_id,sal) in
(select dept_id,max(sal) as maxsal
from emps
where dept_id is not null
group by dept_id)
order by dept_id;

2from子查询(行内视图)

from子查询
从查询的查询结果，再查询

from子查询

求平均工资最低的工资岗位代码

-- 1.按job_id分组求平均工资
-- 2.平均工资最小值
-- 3.用平均工资最小值过滤岗位代码
select job_id,avg(sal) avgsal
from emps
group by job_id;

select min(avgsal) from (
select job_id,avg(sal) avgsal
from emps
group by job_id) t;

select job_id,avg(sal) lowsal
from emps
group by job_id
having lowsal=(select min(avgsal) from (
select job_id,avg(sal) avgsal
from emps
group by job_id) t);

手下人数最多的主管id

-- 1.按mgr_id分组求人数
-- 2.求人数的最大值
-- 3.用人数过滤查询主管id
select mgr_id,count(*) c
from emps
where mgr_id is not null
group by mgr_id
order by c;
-- 多行函数不能和其他字段一块查，要加过滤条件才行
select max(c) from (select mgr_id,count(*) c
from emps
where mgr_id is not null
group by mgr_id
order by c) renshu;

select mgr_id,count(*) c
from emps
where mgr_id is not null
group by mgr_id
having c=14;

select mgr_id,count(*) c
from emps
where mgr_id is not null
group by mgr_id
having c=(
select max(c) from (select mgr_id,count(*) c
from emps
where mgr_id is not null
group by mgr_id
order by c) renshu) ;

3select 子查询