Mysql关键字之Group By(一)

时间：2021-04-04 03:51:21

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

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`;

执行的结果是：

Mysql关键字之Group By(一)

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

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

执行的结果是：

Mysql关键字之Group By(一)

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

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

执行的结果是：

Mysql关键字之Group By(一)

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

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

执行的结果是：

Mysql关键字之Group By(一)

这样的结果可能会使人困惑，我们以第一个sql为例，解释下sql执行的过程：
Mysql关键字之Group By(一)

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

Group By还能怎么用

与order by结合在一起使用

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

我们可以很自然的写出下面的sql:

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

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

Mysql关键字之Group By(一)

通过观察，我们可以发现，事实上，这个 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;

执行的结果是：

Mysql关键字之Group By(一)

与having结合在一起使用

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

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

执行的结果为：

Mysql关键字之Group By(一)

这里需要注意一个问题： 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;

执行的结果如下：

Mysql关键字之Group By(一)

Group By与Limit

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

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

执行的结果如下：

Mysql关键字之Group By(一)

我们需要理解的是： group by 分组的依据，以及 where过滤条件作用的粒度
如果你觉得你已经理解了 group by关键字的用法，欢迎移步至 Mysql关键字之Group By(二)，有点小练习在等着你。。。

标签：

相关文章