原创作品,允许转载,转载时请务必以超链接形式标明文章 原始出处 、作者信息和本声明。否则将追究法律责任。http://blog.csdn.net/github_27109687/article/details/73810951-Test_huhy博客
一、题目
Student 学生表
字段名称 | 字段类型 | 字段含义 |
---|---|---|
S# | Int | 学号 |
Sname | varchar | 学生姓名 |
Sage | varchar | 学生年龄 |
Ssex | varchar | 学生性别 |
Course课程表
字段名称 | 字段类型 | 字段含义 |
---|---|---|
C# | Int | 课程号 |
Cname | varchar | 课程名 |
SC成绩表
字段名称 | 字段类型 | 字段含义 |
---|---|---|
S# | Int | 学号 |
C# | Int | 课程号 |
score | float | 成绩 |
查询平均成绩大于60分,姓:‘王’且课程名为:‘计算机’的同学的学号和平均成绩。
二、解答
Mysql SQL语句如下:
select t1.S#, ROUND(avrg,2) from (
select sc.S#,avg(sc.score) avrg from rss.SC sc group by sc.S#
) t1,rss.Student s,rss.SC sc,rss.Course c
where s.S#=t1.S#
and sc.S#=t1.S#
and c.C#=sc.C#
and avrg > '60'
and s.Sname like '王%'
and c.Cname = '计算机';
三、总结与分析
(1)分析
- 首先根据成绩表里算出每个学生的平均成绩,组合成一个有学生id和学生平均成绩的t1表
- 由于条件需要姓‘王’的,则需要Student表的Sname;条件要求课程名是‘计算机’,则需要Course表的Cname;要求平均分大于60,则t1的表必不可少;同时需要SC示关联表。所以t1表和数据库里的三个表做关联连接。
3.那最后关联成的一张大表里,如上方的草图所见。一般合理的情况下,成绩表里不会出现某个学生的某个课程有2个成绩分数,为了避免这种情况的发生,最后可以加上一句‘GROUP BY t1.S#’或者进行distinct去重。去重distinct方法的SQL语句如下:
select DISTINCT t1.S#, ROUND(avrg,2) from (
select sc.S#,avg(sc.score) avrg from rss.SC sc group by sc.S#
) t1,rss.Student s,rss.SC sc,rss.Course c
where s.S#=t1.S#
and sc.S#=t1.S#
and c.C#=sc.C#
and avrg > '60'
and s.Sname like '王%'
and c.Cname = '计算机';
(2)总结
SQL中出现count()、avg()、sum()等函数时,一般都要注意考虑是否需要进行分组(GROUP BY)。比如上方的平均成绩,如针对上面每个学生所有课的平均成绩,肯定是需要根据用户id进行分组再求平均成绩;而如果一个班级就一门课的情况下,求整个班平均成绩的话,则可不需要用到分组。
若有遗漏或者不正确的地方,请各位多多指教~~~