一些比较难的SQL语句查询

版权声明:本文为博主原创文章,遵循 CC 4.0 by-sa 版权协议,转载请附上原文出处链接和本声明。
本文链接:https://blog.csdn.net/tnt87/article/details/78241815

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;

展开阅读全文

没有更多推荐了,返回首页