子查询

#子查询：把一个查询语句用括号括起来，当做另外一条查询语句的条件去用，称为子查询

select emp.name from emp inner join dep on emp.dep_id = dep.id where dep.name="技术";




select name from emp where dep_id =
(select id from dep where name="技术");


#查询平均年龄在25岁以上的部门名
select name from dep where id in
(select dep_id from emp group by dep_id having avg(age) > 25);

select dep.name from emp inner join dep on emp.dep_id = dep.id
    group by dep.name
    having avg(age) > 25;

#查看不足2人的部门名(子查询得到的是有人的部门id)



select * from emp where exists (
    select id from dep where id > 3
);


#查询每个部门最新入职的那位员工

select t1.id,t1.name,t1.post,t1.hire_date,t2.post,t2.max_date from emp as t1 inner join
(select post,max(hire_date) as max_date from emp group by post) as t2
on t1.post = t2.post
where t1.hire_date = t2.max_date
;