mysql 数据库group by 面试题（一）

目录

一、什么是Group By以及用法有哪些

二、查询语句中select from where group by having order by的执行顺序

三、group by基础面试题举例

---

关于mysql的group by关键字大家一定不陌生，在平时的开发过程中经常会用到mysql的group by分组的用法，用来获取数据表中以分组字段作为依据统计数据。下面我们通过一个面试题对group by进行一个简单的了解。

一、什么是Group By以及用法有哪些

group by通俗一点的讲就是通过by后面指定的数据库表字段把数据进行分组，所谓的分组就是把一个大的“数据集合”划分为若干个“小区域数据集合”，最后针对这个“小区域数据集合”进行数据处理。group by通常和聚合函数结合使用，比如count()、sum()等聚合函数。

1、使用group by有几点需要注意的地方：

（1）、group by查询语句中select后面字段必须是by后面的分组字段，如果其他的字段想出现在select中必须要和聚合函数一起使用。

（2）、group by查询语句中返回分组后每个组的第一条数据。

（3）、group by多字段分组是将具有相同多个字段的数据放到同一个分组中，比如group by a，b，意思是将所有具有a字段值和b字段值的数据放到一个分组里。

（4）、group by常用的五种聚合函数：

max(列名)：求最大值； 

min(列名)：求最小值；

sum(列名)：求和；

avg(列名)：求平均值；

count(列名)：统计记录的条数；

2、group by与where联合使用：

当where和group by一起使用的时候，where一定是放在group by前面的，也就是说先对select xxx from xxx的数据集合根据where条件筛选，然后在使用group by对筛选后的数据进行分组，最后得到我们想要的数据集合。注意：where后的条件表达式不允许使用聚合函数。

3、group by和having联合使用：

having只能用在group by之后，对分组以后的数据集结果进行筛选，也就是说使用having的前提条件就是分组。另外having可以和聚合函数一起使用，比如having sum(x)>1000。

4、group by和order by联合使用：

当group by和order by一起使用的时候，会先执行group by进行分组，然后将分组好的数据集进行order by排序，order by x中的x是select 查询字段的某一个字段，order by的位置是放在having后面的。

order by 字段名 desc：降序排列

order by 字段名 asc：升序排列

5、group by和limit联合使用：

group by和limit联合使用时，会先执行group by分组，然后从分组好的数据集合中通过limit取出几条数据，注意，limit的位置要放在order by后面。

limit x 从第一条数据开始，取出x条数据；

limit x,y 从第x条数据开始，取出y条数据；

6、group by和group_concat()函数联合使用：

group by和group_concat()函数一起使用的话，可以将group by分组好的某个字段的数据拼接到一起，默认通过“,”拼接。并且group_concat()函数还可以针对某个字段数据进行内部排序，将最终的排序结果返回。

二、查询语句中select from where group by having order by的执行顺序

在查询语句中用到的关键词主要有六个，并且它们的书写顺序依次为：select--from--where--group by--having--order by。但是，在mysql数据库中这六个关键词执行顺序和书写顺序是不一样的，具体的执行顺序为：from--where--group by--having--select--order by。

1、from xxx从哪个表中获取数据，然后通过where xxx查询条件获取第一个结果集A；

2、将上面的结果集A通过group by xxx分组获取到第二个结果集B；

3、将结果集B通过having xxx进行筛选，获取到第三个结果集C； 

4、将结果集C通过select xxx 查看某个字段或者某个字段计算的结果，生成结果集D；

5、将结果集D通过order by进行排序，返回最终的查询结果；

三、group by基础面试题举例

1、创建表并插入数据：

CREATE TABLE `tb_student` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'id',
  `name` varchar(15) DEFAULT NULL COMMENT '姓名',
  `subject` varchar(15) DEFAULT NULL COMMENT '学科',
  `score` int(3) DEFAULT NULL COMMENT '分数',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8;


INSERT INTO `tb_student` (`name`, `subject`, `score`) VALUES ('张三', '数学', '90');
INSERT INTO `tb_student` (`name`, `subject`, `score`) VALUES ('张三', '语文', '50');
INSERT INTO `tb_student` (`name`, `subject`, `score`) VALUES ('张三', '英语', '40');
INSERT INTO `tb_student` (`name`, `subject`, `score`) VALUES ('李四', '数学', '45');
INSERT INTO `tb_student` (`name`, `subject`, `score`) VALUES ('李四', '语文', '55');
INSERT INTO `tb_student` (`name`, `subject`, `score`) VALUES ('王五', '数学', '30');
INSERT INTO `tb_student` (`name`, `subject`, `score`) VALUES ('王五', '语文', '70');
INSERT INTO `tb_student` (`name`, `subject`, `score`) VALUES ('李四', '英语', '80');
INSERT INTO `tb_student` (`name`, `subject`, `score`) VALUES ('王五', '英语', '49');

问题一：使用group by分组查询每个学生有几科不及格？sql语句如下：

-- 注：先通过where查出不及格的学生，然后通过group by分组计算每个学生有几科不及格
select name,count(*) from tb_student where score < 60 group by name;

问题二：查出学生不及格科目的平均值是多少？sql语句如下：

-- 注：通过聚合函数avg()计算平均值
select name,avg(score) from tb_student where score < 60 group by name;

问题三：查询每个学生每科成绩的最大值，并倒序展示？sql语句如下：

-- 注：先通过group by结合max()聚合函数查出结果集，然后在通过order by排序
select name,max(score) as score from tb_student group by name order by score desc;

问题四：使用group_concat()函数将不及格学生的成绩详情排序展示？sql语句如下：

select name,group_concat(score order by score desc) as score from tb_student where score < 60 group by name;

问题五：使用group by 和 having查询两门以及两门以上不及格同学的平均分，sql语句如下：

-- 注：使用sum(score<60)来计算不及格的科目数
select name,avg(score) as score,sum(score<60) as gk from tb_student group by name having gk >= 2;