一些比较难的SQL语句查询
1查询各科成绩都高于90分的人。
有一张表：
stu_course:
mysql>select t1.stu_id,t1.no from (select stu_id,count(*) no from stu_course group by stu_id)t1 inner join (select stu_id,count(*) no from stu_course where score >=90 group by stu_id)t2 on t1.stu_id = t2.stu_id and t1.no = t2.no;
mysql> select distinct tb_1.stu_id,
tb_1.name from stu_course tb_1where (select min(tb_2.score) from stu_course tb_2 where tb_2.stu_id =tb_1.stu_id)>=90;
2查询每个部门薪水最高的人
有两张表:
employees
departments
mysql> select *from employees e where(e.department_id,e.salary)
in
(select e1.department_id,max(e1.salary) from employees e1 group by department_id) ;
mysql> select *from employees e where
e.salary =(select max(e1.salary) from employees e1 where e1.department_id = e.department_id) ;
3查询每个部门薪水最高的前二人。
使用的表就是题2的表
mysql> select * from employees e1 where (select count(*) from employees e2 where e2.department_id=e1.department_id and e2.salary > e1.salary)<2 order by department_id;