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