【面经】MySQL题目

1、一门课程取出成绩排名前五的学生

题1：如果只有一门课程，取出成绩排名前五的学生

2：多门课程取出成绩前三

参考

2.1 只考虑人数，不考虑并列

select s1.* from scores s1
where (select count(1) from scores s2 where s1.subject=s2.subject and s1.score<s2.score)<3
order by s1.subject,s1.score desc;

解析：
select count(1) from scores s2 where s1.subject=s2.subject and s1.score<s2.score意思是：表s1外层查询每查询一次，再到内层循环中查询表s2，当课程名相同时，count()统计 s1.score<s2.score 即表s2中成绩大于s1的人数有几人，保持不超过三人，那自己就是第三名

查询结果：

2.2 考虑并列

select s1.name,s1.subject,s1.score from scores s1
left join (select distinct subject,score from scores) s2
on s1.subject=s2.subject
and s1.score<s2.score
group by s1.name,s1.subject,s1.score
having count(1)<3
order by subject,score desc;

解析：
使用 distinct 关键字对表s2中存在多名同学分数相同的情况进行去重，从而达到并列排名的目的。
需要注意的是，由于groub by 的条件是表s1中的字段，所以 count(1) 统计的是表s1中每次查询s1.score<s2.score 成绩低于表s2的学生人数，满足条件小于3，就可以取出前三名

查询结果：

一些别的

3、男性平均年龄大于20碎的城市

select city,avg(age)  from t where gender="男性" group by city having avg(age) > 20

4、部门工资最高的员工

select d.name 'Department',e.name 'Employee',e.salary 'Salary'
from Employee as e,Department as d
where e.DepartmentId=d.id
and (e.Salary,e.DepartmentId) in 
(select max(Salary),DepartmentId from Employee group by DepartmentId)

5、部门工资前三高的员工

select d.Name as Department,e1.Name as Employee,e1.Salary as Salary
from Employee as e1
left join Department as d 
on e1.DepartmentId=d.id 
where 3>(
    select count(distinct e2.salary)
    from Employee e2
    where e1.DepartmentId = e2.DepartmentId
    and e1.salary<e2.salary
)

6、查询平均分大于等于60的同学

查 id

select id,avg(score) as avg_score 
from sc
group by sid
having avg_score>=60

查姓名

select id,avg(score) as avg_score,name
from sc,student
where sc.sid = student.sid
group by sid
having avg_score>=60