文章目录
- 21、查询不同老师所教不同课程平均分从高到低显示
- 23、使用分段[100,85),[85,70),[70,60),[<60] 来统计各科成绩，分别统计各分数段人数：课程ID和课程名称
21、查询不同老师所教不同课程平均分从高到低显示
以课程为主题：两表联合，以课程分组，求平均值（假如不同的老师教同一门课，这种情况不予考虑）
select a.c_id '课程号',b.c_name '课程名', avg(a.s_score) '平均分'
from score as a
inner JOIN course as b
on a.c_id = b.c_id
group by a.c_id
order by avg(a.s_score) desc
以老师为主题
- 需要三表联合，老师进行分组。再次考虑平均分
select t.t_id,t.t_name,c.c_name,avg(s.s_score)
from teacher as t
inner join course as c
on t.t_id = c.c_id
inner join score as s
on c.c_id = s.c_id
group by t.t_id
order by avg(s.s_score) desc
23、使用分段[100,85),[85,70),[70,60),[<60] 来统计各科成绩，分别统计各分数段人数：课程ID和课程名称
- 对课程号分组，使用case when来判断
select s.c_id '课程号' ,c.c_name '课程名称',
sum(case when s.s_score <= 100 and s.s_score > 85 then 1 else 0 End) as '[100,85)',
count(case when s.s_score <= 85 and s.s_score > 70 then 1 else NULL End) as '[85,70)',
sum(case when s.s_score <= 70 and s.s_score > 60 then 1 else 0 End) as '[70,60)',
sum(case when s.s_score < 60 then 1 else 0 End) as '[< 60)'
from score as s
INNER JOIN course as c
on s.c_id = c.c_id
group by c.c_id
24、查询学生平均成绩及其名次
select st.s_id,st.s_name,avg(s.s_score),row_number() OVER(order by avg(s.s_score) desc)
from
student as st
inner JOIN score as s
on st.s_id = s.s_id
group by st.s_id
