相关表数据:
1、score_info
2、student_info
题目及思路解析:
分组结果的条件
1、查询平均成绩大于60分的学生的学号和平均成绩
代码:
select
stu_id,
avg(score) score_avg
from score_info
group by stu_id
having score_avg>60;
思路解析:
首先通过学号进行分组,获取到每个学习的所有科目的成绩,接着求平均成绩(所有学科总成绩的平均成绩),最后对求得的平均成绩进行条件筛选,得到符合题目要求的学生信息
结果:
2、查询至少选修四门课程的学生学号
代码:
select
stu_id
from score_info
group by stu_id
having count(course_id)>=4;
或
select
stu_id,
count(course_id) course_count
from score_info
group by stu_id
having course_count >=4;
思路解析:
首先对学号进行分组,获取到每个学生的参加考试学科的成绩信息,用count函数对学科号进行统计,并按照题目条件进行筛选,最终获取符合题意的学生成绩信息
结果:
3、查询同姓(假设每个学生姓名的第一个字为姓)的学生名单并统计同姓人数大于2的姓
注:本题可以使用字符串函数substring()
代码:
select
t1.first_name,
count(*) first_name_count
from
(
select
stu_id,
substring(stu_name,0,1) first_name
from student_info
)t1
group by t1.first_name
having first_name_count >=2;
注:
1、第二层查询可以不用t1.first_name
2、count(*)即统计表中所有行数,包括null (也可以使用count(列名),不包括null)
3、关于subtring补充请点击跳转---> 知识补充
思路解析:
首先我们利用substring()函数获取到每个学生的姓氏,接着我们对获取的姓氏进行分组、并统计分组后的人数,最后按照题目要求进行筛选,获取到符合题意的学生信息
结果:
4、查询每门课程的平均成绩,结果按平均成绩升序排序,平均成绩相同时,按课程号降序排列
代码:
select
course_id,
avg(score) avg_score
from score_info
group by course_id
order by avg_score asc,course_id desc ;
注: 平均成绩升序按排序,后面加上按课程号降序排列,即平均成绩相同时自动按照第二种排序情况
思路解析:
首先对课程号进行分组(获取到每个课程的学生成绩),对获取的课程成绩(总成绩)求平均值,最后对结果进行排序,平均成绩按升序,平均成绩相同时,按课程号降序排列 。
结果:
5、统计参加考试人数大于等于15的学科
代码:
select
course_id,
count(*) number_test
from score_info
group by course_id
having number_test>=15;
思路解析:
首先按照课程号进行分组,接着统计每个课程的参加考试人数,然后添加条件进行进一步筛选,最终获取到符合题意的数据。
结果:
总结归纳:
考察了聚合函数与分组聚合的综合运用,使用了字符串函数,查询简单嵌套等
知识补充:
substring:截取字符串
语法一:substring(string A, int start)
返回值:string
说明:返回字符串A 从start位置到结尾的字符串
语法二:substring(string A, int start, int len)
返回值:string
说明:返回字符串A从start位置开始,长度为len的字符串