【MySQL】子查询

关键字子查询 //生成单列表作为IN条件

带IN的子查询

查询平均年龄25岁以上的部门名

select * from dep where id IN    

(select dep_id from employee

group by dep_id

having avg(age) > 25);

 

带运算符的子查询

查询技术部员工的姓名

select name from employee where dep_id = (

select id from dep where name=’技术部’)

 

查看不足1人的部门名

select name from dep where id in

(select dep_id from employee group by dep_id having count(id)<1);

 

查询大于所有人平均年龄的员工名与年龄

select name, age from employee where age > (select avg(age) from employee);

 

带EXISTS的子查询

select * from employee

where EXISTS

(select id from department where name = ‘技术’)

 

生成临时表

(select id from department where name = ‘技术’) as t1

 

每个部门最新入职的员工

select name,hire_day from employee as t1

inner join

(select dep_id, max(hire_day) as hire_day from emoloyee group by dep_id) as t2

on t1.dep_id = t2.dep_id

where t1.hire_day = t2.hire_day;