Mysql关键字之Group By

Group By是一个我们在日常工作学习过程中经常遇到的一个Mysql关键字。现总结其用法如下。

准备一张表和一些记录

创建学生的成绩表courses:

CREATE TABLE `courses` (
`id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '自增id',
`student` VARCHAR(255) DEFAULT NULL COMMENT '学生',
`class` VARCHAR(255) DEFAULT NULL COMMENT '课程',
`score` INT(255) DEFAULT NULL COMMENT '分数',
PRIMARY KEY (`id`),
UNIQUE KEY `course` (`student`, `class`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

该表记录了学生某节课的考试分数。
往courses表中插入记录：

INSERT INTO `courses`(`student`, `class`, `score`) VALUES('A', 'Math', 90);
INSERT INTO `courses`(`student`, `class`, `score`) VALUES('A', 'Chinese', 80);
INSERT INTO `courses`(`student`, `class`, `score`) VALUES('A', 'English', 70);
INSERT INTO `courses`(`student`, `class`, `score`) VALUES('A', 'History', 80);

INSERT INTO `courses`(`student`, `class`, `score`) VALUES('B', 'Math', 73);
INSERT INTO `courses`(`student`, `class`, `score`) VALUES('B', 'Chinese', 60);
INSERT INTO `courses`(`student`, `class`, `score`) VALUES('B', 'English', 70);
INSERT INTO `courses`(`student`, `class`, `score`) VALUES('B', 'History', 90);

INSERT INTO `courses`(`student`, `class`, `score`) VALUES('C', 'Math', 70);
INSERT INTO `courses`(`student`, `class`, `score`) VALUES('C', 'Chinese', 50);
INSERT INTO `courses`(`student`, `class`, `score`) VALUES('C', 'English', 20);
INSERT INTO `courses`(`student`, `class`, `score`) VALUES('C', 'History', 10);

INSERT INTO `courses`(`student`, `class`, `score`) VALUES('D', 'Math', 53);
INSERT INTO `courses`(`student`, `class`, `score`) VALUES('D', 'Chinese', 32);
INSERT INTO `courses`(`student`, `class`, `score`) VALUES('D', 'English', 99);
INSERT INTO `courses`(`student`, `class`, `score`) VALUES('D', 'History', 100);

 

Group By有什么用

我们使用几个简单的例子看一下Group By的作用:

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

执行的结果是：

类似地，我们按照score对记录进行分组：

SELECT * FROM `courses` GROUP BY `score`;

执行的结果是：

我们甚至可以对多个字段进行group by：

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

执行的结果是：

最后，我们交换字段顺序对记录进行分组：

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

执行的结果是：

这样的结果可能会使人困惑，我们以第一个sql为例，解释下sql执行的过程：

该sql首先会按照class进行分组得到四张中间表，然后输出的时候将每一个分组的第一个记录组合在一起形成了最终的结果。我们还可以发现，最终的记录是按照class进行排序的。这样的顺序并不可靠，具体形成的原因恐怕需要在Mysql的底层原理中找到答案。

 

Group By还能怎么用

与order by结合在一起使用

我们需要学生的成绩表，且每个学生每科的成绩按照由大到小的顺序排列：

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

然而，执行的结果貌似并不是我们想要的：

通过观察，我们可以发现，事实上，这个sql是将所有的记录按照score由大到小的顺序排列了，为什么会出现这样的结果呢？
事实上，这个取决于整个sql的执行顺序，真正的执行顺序是 from ... where ... group by ... order by ... select，order by作用在整个记录，而不是每个分组上。
那么，怎么样能够得到我们期望的结果呢？这里给出我的sql实现：

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

执行的结果是： 

与having结合在一起使用

我们需要得到所有功课平均分达到60分的同学和他们的均分：

SELECT `student`, AVG(`score`) AS`avg_score`
FROM `courses`
GROUP BY `student`
HAVING AVG(`score`) >= 60
ORDER BY `avg_score` DESC;

执行的结果为：

这里需要注意一个问题：where 与 having的区别。where作用于所有的记录，而having则作用于一个分组。
举例说明：

假设我们这里需要得到所有功课（除历史课）平均分达到60分的同学和他们的均分：

 

SELECT `student`, AVG(`score`) AS `avg_score`
FROM `courses`
WHERE `class` <> 'History'
GROUP BY `student`
HAVING AVG(`score`) >= 60
ORDER BY `avg_score` DESC;

Group By与Limit

我们需要列出均分最高的三门课：

SELECT `class`, AVG(`score`) AS `avg_score`
FROM `courses`
GROUP By `class`
ORDER BY `avg_score` DESC
LIMIT 3;

执行的结果如下： 

我们需要理解的是：group by分组的依据，以及where过滤条件作用的粒度