(SQL)--查询平均成绩大于等于 60 分的同学的学生编号和学生姓名和平均成绩

• 题型一

题目描述

查询平均成绩大于等于 60 分的同学的学生编号和学生姓名和平均成绩

写法一：

先从成绩表分组过滤平均成绩大于60的学生id和成绩。再和学生表连接，获取学生姓名

select student.s_id,student.s_name,a.avg from student
inner join (
    select s_id,avg(s_score) from score 
    group by s_id having avg(s_score)>=60
) a
on student.id=a.s_id

写法二：

直接连接两表

select a.s_id,a.s_name,avg(b.s_score) as avg_score
from student a join score b
on a.s_id=b.s_id
group by 
    a.s_id
    a.s_name
having
 avg_score>=60

• 题型二（简单）

查询平均成绩大于等于 60 分的同学的学生编号和平均成绩

select s_id,avg(s_score)
from score
group by s_id
having avg(s_score)>=60