关键词（Key Words）

• MySQL 综合实践
• 三表联查 inner join
• 聚合查询avg,sum,Group by
• 排序order by
• 查询格式化cast...as...
• 条件判断case...when...

开发环境

• 系 统：CentOS Linux release 7.5.1804 (Core)
• MySQL版本：MySQL 5.6

问题场景描述

在掘金，有一位朋友@Horizon757分享了一份这样的SQL的训练题，借用此题进行MySQL实践：

参考答案

先说答案，再请耐心细看下面的解题思路。

select 	name,	cast(sum(credit*(case          when grade < 60 then 0          else grade/10-5 end))/sum(credit)          as decimal(3,2) )      as avg_gpa from t_student a inner join t_grade b on  a.id = b.sid inner join t_course c on b.cid = c.id group by sid order by avg_gpa desc;复制代码

解题思路

1. 进入MySQL数据库

mysql -h localhost -u root -p复制代码

1. 定义数据库及数据表（DDL）

• 新建一个数据库；

  create database school;复制代码

• 新建三个数据表t_student , t_course , t_grade

  • t_student 表中，id 作为主键，name 作为必须项，不能为空(not null)；

    create table t_student (    id(11) int not null auto_increment primary key,    name varchar(10) not null) engine=InnoDB default charset=utf8;复制代码

  • t_course 表中，id 作为主键，name 作为必须项，不能为空，credit 是小数用 decimal(2,1) ，默认值0.0；

    create table t_course (    id int(11) not null auto_increment primary key,    course varchar(5) not null,    credit decimal(2,1) default 0.0 ) engine=InnoDB default charset=utf8;复制代码

  • t_grade 表中，sid 与 cid 作为t_grade 表的联合主键，确保一位学生在一个课程中只能有一个成绩；同时，sid 作为 t_student 的外键，cid作为 t_course 的外键，两者在我的理解中他们的限制条件 on delete 需要有些不同，当删除一位学生时，他的成绩级联一起被删除是很好的；但是想要删除一门课程时，假如这门课程已经学生选修这门课并且获得了成绩，在删除时就必须做出限制，提示不能删除该门课程；grade的分数看起来

    create table t_grade (    sid int(11) not null,    cid int(11) not null,    grade int(2) default 0,primary key (sid,cid),     foreign key (sid) references t_student(id)     on update cascade on delete cascade,    foreign key (cid) references t_course(id)     on update cascade on delete restrict) engine=InnoDB default charset=utf8;复制代码

1. 插入数据（DML）

• 从 t_student 表中插入信息:

  insert into t_student (name) values ('S1'),('S2'),('S3');复制代码

• 从 t_course 表中插入信息:

  insert into t_course (course,credit) values ('A',4.0),('B',3.0),('C',2.0);复制代码

• 从 t_grade 表中插入信息:

  insert into t_grade (sid,cid,grade) values (1,1,95),(1,2,90) ,(1,3,85),(2,1,85),(2,2,97),(2,3,90),(3,1,60),(3,2,50),(3,3,59);复制代码

  这里插入完之后，就是题图中的数据表所呈现的信息了；

  联合查询下，下面是我们这次所要用到所有基本信息：

  select 	name,course,credit,grade from t_grade a left join t_student b on a.sid = b.idleft join t_course c on a.cid = c.id;复制代码

1. 查询数据（DML）

• 用一条 SQL 查询各位学生的平均学分绩点（四舍五入精确两位小数），并从高到低排序：

  • 首先我们看看绩点是如何计算的：绩点=分数/10-5，并且小于60分，绩点为0，使用case...when...，SQL 语句表示就是：

    select 	sid,	case 		when grade < 60 then 0 		else grade/10-5 end 	as gpa from t_grade;复制代码

  • 再看看使用 SQL 怎么样一步步查询出平均绩点：

    平均绩点=（课程学分1绩点+课程学分2绩点+...+课程学分n绩点）/（课程学分1+课程学分2+...+课程学分n绩点）

    • 计算每个学生的平均绩点，所以要按照学生进行分组, 即 group by sid；

    • 这里需要提到 group by sid 使用的时候呈现数据的方式，如果在不使用聚合函数的时候，单单使用 group by sid 时，对比上一个图我们可以发现，查询的数据是显示每一位同学第一条记录在数据表中的数据，并且多几位小数点：

      select 	sid,	case 		when grade < 60 then 0 		else grade/10-5 end 	as gpa from t_grade group by sid;复制代码

    • 如果按照最简单的求每一个同学所有成绩的平均值，那就很简单，但这不是最终答案：

      select 	sid,	avg(case         when grade < 60 then 0         else grade/10-5 end)     as gpa from t_grade group by sid;复制代码

      在这里我发现结合使用 group by 就可以实现遍历计算的效果；

    • 我试着利用 group by 求出每个同学所修课程的总学分 ，发现可行！

      select 	sid,	sum(credit) from t_grade a left join t_course b on a.cid = b.id group by sid order by sid ;复制代码

    • 我试着利用 sum 和 group by 求出 （课程学分1绩点+课程学分2绩点+...+课程学分n*绩点），发现也可行！！

      select 	sid,	sum(credit*(case         when grade < 60 then 0         else grade/10-5 end))     as gpa from t_grade a left join t_course b on a.cid = b.id group by sid;复制代码

    • 接着顺利求出平均绩点：

      select 	sid,	sum(credit*(case         when grade < 60 then 0         else grade/10-5 end))/sum(credit)     as gpa from t_grade a left join t_course b on a.cid = b.idgroup by sid;复制代码

      与在Excel验证的结果相符：

    • 接着利用cast...as..将平均绩点四舍五入，小数点精确到两位数，利用order by从高到低排序：

      select 	name,	cast(sum(credit*(case         when grade < 60 then 0         else grade/10-5 end))/sum(credit)     	as decimal(3,2)) 	as avg_gpa from t_grade a left join t_course b on a.cid = b.id left join t_student c on a.sid = c.id group by sid order by avg_gpa desc;复制代码

    • 在这里遇到小问题，cast(exp as decimal(3,2)) is ok , but cast(exp as float(3,2)) and cast(exp as float) is failed，可能是float 不能在cast中使用

    • 6、优化

      根据@Horizon757 老师提供的建议，将主表改为t_student，将left join 改为 inner join 提高效率

      select 	name,	cast(sum(credit*(case          when grade < 60 then 0          else grade/10-5 end))/sum(credit)          as decimal(3,2) )      as avg_gpa from t_student a inner join t_grade b on  a.id = b.sid inner join t_course c on b.cid = c.id group by sid order by avg_gpa desc;复制代码

  以上我探索出来的参考答案，可能有一些优化的地方我没有想到，希望和大家交流。接下来继续join优化器的知识，希望可以和大家分享一些查询优化的笔记。

  非常感谢@Horizon757老师对我的耐心指导。

  ——57EN写于2018年12月28号，修改于2019年2月13日。