Mysql—分组查询详解（group by & having）（九）

一、常见聚合函数

函数名称	作用
max	查询指定列的最大值
min	查询指定列的最小值
count	统计查询结果的行数
sum	求和，返回指定列的总和
avg	求平均值，返回指定列数据的平均值

二、分组查询

语法：

英文版：
select column,group_function,... 
from table
[where condition]
group by group_by_expression
[having group_condition]; 

中文版：
select 字段,聚合函数,...
from 表名
[where 条件]
group by 分组表达式
having 分组过滤条件

说明：

• group_function：聚合函数
• group_by_expression：分组表达式，多个之间用逗号隔开
• group_condition：分组之后对数据进行过滤
• 分组中，select后面只能有两种类型的列：（特别重要）
  1、出现在group by后的列
  2、或者使用聚合函数的列

1、准备数据

2、单字段分组

需求 ：查询每个用户下单数量，输出：用户id、下单数量，如下：

3、多字段查询

需求 ：查询每个用户下单数量，输出：用户id、年份、下单数量，如下：

三、where和having的区别

• where是在分组（聚合）前对记录进行筛选，
• having是在分组结束后的结果里筛选，最后返回整个SQL的查询结果

可以把having理解为两级查询，即含having的查询操作 先获得不含having子句时的SQL查询结果表，然后 在这个结果表上使用having条件筛选出符合的记录， 最终返回这些记录。

因此 having后是可以跟聚合函数的，并且这个聚合函数不必与select后的聚集函数相同。

1、分组前筛选数据

分组前对数据进行筛选，使用where关键字

需求 ：查询2018年每个用户下单数量，输出：用户id、下单数量，如下：

2、分组后筛选数据

分组后对数据筛选，使用having关键字

需求 1 ：查询2018年订单数量大于1的用户，输出：用户id、下单数量，如下：

需求 2 ：获取每个用户最大金额然后按照最大金额倒序，输出：用户id、最大金额，如下：

四、where、group by、having、order by 、limit

where、group by、having、order by、limit这些关键字一起使用时，先后顺序有明确的限制，语法如下：

select  列
from 表
where [查询条件]
group by [分组表达式]
having [分组过滤条件]
order by [排序条件]
limit [offset,] count;

注意：

• 写法上面必须按照上面的顺序来写
• 完整的执行顺序：from > where > group by > having > select > order by > limit

下面摘自网上某位博主，地址：引用地址

（8）SELECT（9）DISTINCT <select_list>   
（1）FROM <left_table>
（3）<join_type> JOIN <right_table>
（2）ON <join_condition>
（4）WHERE <where_condition>
（5）GROUP BY <grout_by_list>
（6）WITH {CUTE|ROLLUP}
（7）HAVING <having_condition>
（10）ORDER BY <order_by_list>
（11）LIMIT <limit_number>

示例：

***需求 *** ：查询2018年，下单数量大于等于2的，按照下单数量降序，最后只输出一条记录，显示：用户id、下单数量，如下：

五、mysql分组中的坑

在本文开头的分组查询中有介绍，分组中select后面的列只能有2种：

• 出现在group by后面的列
• 使用聚合函数的列

oracle、sqlServer、db2中也是按照这种规范来的。而文中使用的是5.6版本（及以下版本），不是按照这种规则，select后面可以跟任何合法的列。如下：

5.7及以上都是默认在分组时select后面只能跟那两种字段。

而这种规范是Mysql中表sql_mode控制着。我们可以查看一下sql_mode的配置：

这是5.6版本的配置信息，发现这里面只有一个配置规则：STRICT_ALL_TABLES。而表示select后面的列必须符合上面两点规范的配置规则为：ONLY_FULL_GROUP_BY。

我们去修改Mysql中的my.ini文件：

sql_mode=STRICT_ALL_TABLES,ONLY_FULL_GROUP_BY

就报错了。（虽然这个报错的理由不是表示select后面跟的列与分组不符合的错误）

下面我们可以大概了解一下sql_mode下面有哪些配置以及含义：

1、ONLY_FULL_GROUP_BY：
出现在select语句、HAVING条件和ORDER BY语句中的列，必须是GROUP BY的列或者依赖于GROUP BY列的函数列。

2、NO_AUTO_VALUE_ON_ZERO：
该值影响自增长列的插入。默认设置下，插入0或NULL代表生成下一个自增长值。如果用户
希望插入的值为0，而该列又是自增长的，那么这个选项就有用了。

3、STRICT_TRANS_TABLES：
在该模式下，如果一个值不能插入到一个事务表中，则中断当前的操作，对非事务表不做限制

4、NO_ZERO_IN_DATE：
这个模式影响了是否允许日期中的月份和日包含0。如果开启此模式，2016-01-00是不允许的，但是0000-02-01是允许的。它实际的行为受到 strict mode是否开启的影响1。

5、NO_ZERO_DATE：
设置该值，mysql数据库不允许插入零日期。它实际的行为受到 strict
mode是否开启的影响2。

6、ERROR_FOR_DIVISION_BY_ZERO：
在INSERT或UPDATE过程中，如果数据被零除，则产生错误而非警告。如
果未给出该模式，那么数据被零除时MySQL返回NULL

7、NO_AUTO_CREATE_USER：
禁止GRANT创建密码为空的用户

8、NO_ENGINE_SUBSTITUTION：
如果需要的存储引擎被禁用或未编译，那么抛出错误。不设置此值时，用默认的存储引擎替代，并抛出一个异常

9、PIPES_AS_CONCAT：
将”||”视为字符串的连接操作符而非或运算符，这和Oracle数据库是一样的，也和字符串的拼接函数Concat相类似

10、ANSI_QUOTES：
启用ANSI_QUOTES后，不能用双引号来引用字符串，因为它被解释为识别符

---

建议：在写分组查询的时候，最好按照标准的规范来写（特别在5.7之后），select后面出现的列必须在group by中或必须使用聚合函数。

总结

1. 在写分组查询的时候，最好按照标准的规范来写，select后面出现的列必须在group by中或必须使用聚合函数；
2. select语法顺序：select、from、where、group by、having、order by、limit，顺序不能搞错了，否则报错。
3. select语法执行顺序：from > where > group by > having > select > order by > limit