Mysql关键字之Group By(二)

原文地址，优先更新https://hhe0.github.io

• 我们在上一节简单介绍了Mysql中group by关键字的用法，没有看过的同学点击这里了解一下;
• 文中提到的courses表和相关记录可以在上一篇文章中自取；
• 给出的所有sql仅供参考，不一定是效率最高的解法，如果大家有其他的方法，也欢迎提供出来，一起讨论。

练习1 那些年一起修过的课

所有的课程

我们的第一反应是可以使用distinct关键字实现

SELECT DISTINCT(`class`) FROM `courses`;!

执行的结果如下：

![ScreenClip.png-2.5kB][1]

事实上，我们同样可以使用group by来实现

SELECT `class` FROM `courses` GROUP BY `class`;

结果与上面相同。因此，我们可以发现group by的一个重要的作用就是枚举出所有满足group by条件的组合，间接达到了去重的效果。

练习2 我要修什么课

所有的学生对应修读的课程

SELECT `student`,`class` 
FROM `courses` 
GROUP BY `student`, `class`; 

执行的结果如下

![ScreenClip.png-10.8kB][2]

练习3 糟糕的成绩单

每门课程的排名表

对应的sql为：

SELECT `class`, `student`, `score`
FROM `courses`
ORDER BY `class`, `score` DESC;

执行的结果如下：

![ScreenClip.png-11.7kB][3]

练习4 推选课代表

参考每门课的最高分选择课代表

对应的sql为

SELECT `c`.`class`, `c`.`student`, `c`.`score`
FROM (
SELECT `class`, MAX(`score`) AS `score`
FROM `courses`
GROUP BY `class`
)`T`
INNER JOIN `courses` `c` ON `T`.`class` = `c`.`class` AND `T`.`score` = `c`.`score`;

执行结果如下

![ScreenClip.png-5kB][4]

练习5 奖学金

为每门课的前三名颁奖

我们容易写出这样的一个sql

SELECT `class`, `student`
FROM `courses`
GROUP BY `class`, `student`
ORDER BY `class`, `score`
LIMIT 3;

很显然，这并不是我们想要的结果，至于为什么结果是这个样子的，大家可以参考上面的order by，这里不再赘述。
正确的sql为

SELECT `c1`.`class`, `c1`.`student`, `c1`.`score`
FROM `courses` `c1`
  INNER JOIN `courses` `c2` ON `c1`.`class` = `c2`.`class` AND `c1`.`student` <> `c2`.`student` AND `c1`.`score` > `c2`.`score`
GROUP BY `c1`.`class`, `c1`.`student`
ORDER BY `c1`.`class`, `c1`.`score` DESC;

再提供一种通用的解法

SELECT `c1`.`class`, `c1`.`student`, `c1`.`score`
FROM `courses` `c1`
INNER JOIN `courses` `c2` ON `c1`.`class` = `c2`.`class` AND `c1`.`score` <= `c2`.`score`
GROUP BY `c1`.`class`, `c1`.`student`
HAVING COUNT(*) <= 3
ORDER BY `c1`.`class`, `c1`.`score` DESC;

上述两个sql执行的结果均为

![ScreenClip.png-9.3kB][6]

两个sql中为什么一个条件是c1.score>c2.score而另一个是c1.score<=c2.score，以及count(*)<=3而不是count(*)<3,可以细细体会下。

练习6 长板与短板

了解每个学生最擅长的科目和最不擅长的科目

对应的sql为

SELECT `T`.`student`, `c`.`class` AS `best_class`, `T`.`max_score` AS `max_score`, `c2`.`class` AS `worst_class`, `T`.`min_score`
FROM (
  SELECT `student`, MAX(`score`) AS `max_score`, MIN(`score`) AS `min_score`
  FROM `courses`
  GROUP BY `student`
) `T`
INNER JOIN `courses` `c` ON `T`.`student` = `c`.`student` AND `c`.`score` = `T`.`max_score`
INNER JOIN `courses` `c2` ON `T`.`student` = `c2`.`student` AND `c2`.`score` = `T`.`min_score`;

执行的结果如下

![ScreenClip.png-9.9kB][7]

练习7 无聊的排序

找到各个分数：Math>Chinese>English>History的同学

对应的sql为

SELECT `c1`.`student`, `c1`.`class`, `c1`.`score`, `c2`.`class`, `c2`.`score`, `c3`.`class`, `c3`.`score`, `c4`.`class`, `c4`.`score`
FROM `courses` `c1`
INNER JOIN `courses` `c2` ON `c1`.`student` = `c2`.`student` AND `c2`.`class` = 'Chinese'
INNER JOIN `courses` `c3` ON `c1`.`student` = `c3`.`student` AND `c3`.`class` = 'English'
INNER JOIN `courses` `c4` ON `c1`.`student` = `c4`.`student` AND `c4`.`class` = 'History'
WHERE `c1`.`class` = 'Math' AND `c1`.`score` > `c2`.`score` AND `c2`.`score` > `c3`.`score` AND `c3`.`score` > `c4`.`score`;

执行的结果为

![ScreenClip.png-4.7kB][8] 想到就更新一些有趣的sql~