MySQL：GROUP BY 分组查询

分组查询是SQL中一个非常强大的功能，它允许我们将数据按照一个或多个字段进行分组，并对每个分组进行聚合计算（如求和、平均值、最大值、最小值等）。在MySQL中，我们使用 GROUP BY 关键字来实现分组查询。

核心语法

SELECT column_1, column_2, ..., AGGREGATE_FUNCTION(column_N)  
FROM table_name  
WHERE condition  
GROUP BY column_1, column_2, ...  
HAVING condition  
ORDER BY column_A;

• SELECT 子句：选择需要显示的列，可以是普通列名，也可以是聚合函数的结果。
• FROM 子句：指定查询的表名。
• WHERE 子句（可选）：在分组前对记录进行筛选。
• GROUP BY 子句：指定分组的列名，MySQL会按照这些列的值将记录分组。
• HAVING 子句（可选）：在分组后对分组结果进行筛选。注意，HAVING通常与聚合函数一起使用，因为WHERE子句无法直接对聚合函数的结果进行筛选。
• ORDER BY 子句（可选）：对查询结果进行排序。

执行顺序

MySQL 会在 FROM 和 WHERE 语句之后，HAVING, SELECT, DISTINCT, ORDER BY 和 LIMIT 子句之前执行 GROUP BY :

单独使用 GROUP BY

单独使用 GROUP BY 关键字时，查询结果会只显示每个分组的第一条记录，相当于 DISTINCT。DISTINCT 相当于一种特殊的分组查询。

与 DISTINCT 不同的是，GROUP BY 会对结果进行排序，而 DISTINCT 不会。如果二者一同使用，查询结果会进行排序。

MySQL 8.0删除了GROUP BY子句的隐式排序。因此，如果使用MySQL 8.0+，会发现上面使用GROUP BY子句查询的结果集没有排序。

GROUP 和 GROUP_CONCAT() 函数

GROUP BY 关键字可以和 GROUP_CONCAT() 函数一起使用。GROUP_CONCAT() 函数会把每个分组的字段值都显示出来。

SELECT sex, GROUP_CONCAT(name) 
FROM student
GROUP BY sex;
'''
+------+----------------------------+
| sex  | GROUP_CONCAT(name)         |
+------+----------------------------+
| 女   | Henry,Jim,John,Thomas,Tom  |
| 男   | Dany,Green,Jane,Lily,Susan |
+------+----------------------------+
'''

多个字段分组查询时，会先按照第一个字段进行分组。如果第一个字段中有相同的值，MySQL 才会按照第二个字段进行分组。

SELECT age,sex GROUP_CONCAT(name)
FROM student
GROUP BY age,sex;
'''
+------+------+--------------------+
| age  | sex  | GROUP_CONCAT(name) |
+------+------+--------------------+
|   21 | 女   | John               |
|   22 | 女   | Thomas             |
|   22 | 男   | Jane,Lily          |
|   23 | 女   | Henry,Tom          |
|   23 | 男   | Green,Susan        |
|   24 | 女   | Jim                |
|   25 | 男   | Dany               |
+------+------+--------------------+
'''

如果第一个字段中的数据都是唯一的，那么 MySQL将不再对第二个字段进行分组。

GROUP 和 聚合函数 aggregate_function()

在数据统计时，GROUP BY 关键字经常和 聚合函数 一起使用。

聚合函数包括 COUNT()，SUM()，AVG()，MAX() 和 MIN()。

• COUNT() 用来统计记录的条数
• SUM() 用来计算字段值的总和
• AVG() 用来计算字段值的平均值
• MAX() 用来查询字段的最大值
• MIN() 用来查询字段的最小值。

SELECT col, aggregate_func(parameters) [AS alias] 
FROM table_name
GROUP BY col;

如下的查询会按照 department_id 对 employees 表中的记录进行分组，并计算每个部门的平均薪资。

SELECT department_id, AVG(salary) AS avg_salary  
FROM employees  
GROUP BY department_id;

GROUP BY 和 表达式

除了对列分组查询之外，也可以借助表达式对行进行分组，允许我们对数据进行更复杂的分组逻辑:

SELECT expression FROM table_name
GROUP BY expression;

• 需要注意的是：GROUP BY 中的表达式要和 SELECT 中的相同

如下的查询按员工的入职年份进行分组，并计算每年入职的员工数。

SELECT YEAR(hire_date) AS hire_year, COUNT(*) AS hires_per_year  
FROM employees  
GROUP BY YEAR(hire_date);

MySQL 和 SQL 标准下的 GROUP BY

• 别名使用：MySQL 允许在 GROUP BY 子句中直接使用 SELECT 列表中的别名，而SQL标准通常不允许这样做，要求直接使用列名或表达式。

• 排序：虽然 GROUP BY 本身不直接指定排序，但结果集可以通过 ORDER BY 进行排序。MySQL允许在 GROUP BY 后直接跟 ASC 或 DESC （这不是标准SQL的做法，且因MySQL版本而异），更标准的做法是在整个查询的末尾使用 ORDER BY。

HAVING 过滤分组

在MySQL中，使用 HAVING 关键字对分组后的数据进行条件筛选，通常与 ORDER BY 一同使用。

语法结构：

SELECT column_ FROM table_name
[ORDER BY column_]
HAVING condition_;

MySQL 会在 FROM, WHERE, SELECT 和 GROUP BY 语句之后，ORDER BY 和 LIMIT 语句之前执行 HAVING 子句:

HAVING 与 WHERE 的异同

HAVING 关键字和 WHERE 关键字都可以用来过滤数据，且 HAVING 支持 WHERE 关键字中所有的操作符和语法。

• 一般情况下，WHERE 用于过滤数据行，而 HAVING 用于过滤分组。

• WHERE 查询条件中不可以使用聚合函数，而 HAVING 查询条件中可以使用聚合函数。

• WHERE 在数据分组前进行过滤，而 HAVING 在数据分组后进行过滤 。

• WHERE 针对数据库文件进行过滤，而 HAVING 针对查询结果进行过滤。
  也就是说，WHERE 根据数据表中的字段直接进行过滤，而 HAVING 根据前面已经查询出的字段进行过滤。

SELECT name, sex FROM student WHERE height>180;
'''可以正常输出'''

SELECT name, sex FROM student HAVING height>180;
'''
报错
ERROR 1054 (42S22): Unknown column 'height' in 'having clause'
'''

• WHERE 查询条件中不可以使用字段别名，而 HAVING 查询条件中可以使用字段别名。

分组查询案例

1. 查询每个部门的平均工资

SELECT AVG(salary), department_id  
FROM employees  
GROUP BY department_id;

这个查询将employees表中的数据按照department_id分组，并计算每个部门的平均工资。

2. 查询每个工种的最高工资

SELECT MAX(salary), job_id  
FROM employees  
GROUP BY job_id;

这个查询将employees表中的数据按照job_id分组，并计算每个工种的最高工资。

3. 添加筛选条件

查询邮箱中包含’a’字符的、每个部门的平均工资：

SELECT AVG(salary), department_id  
FROM employees  
WHERE email LIKE '%a%'  
GROUP BY department_id;

这个查询在分组前添加了筛选条件，只选择邮箱中包含’a’字符的记录进行分组和计算。

4. 复杂筛选条件

查询哪个部门的员工个数大于2：

SELECT COUNT(*), department_id  
FROM employees  
GROUP BY department_id  
HAVING COUNT(*) > 2;

这个查询首先按照 department_id 分组，然后计算每个部门的员工个数，最后通过HAVING子句筛选出员工个数大于2的部门。

5. 按表达式或函数分组

按员工姓名的长度分组，查询每一组的员工个数，筛选员工个数大于5的组：

SELECT COUNT(*) AS c, LENGTH(last_name) AS len_name  
FROM employees  
GROUP BY LENGTH(last_name)  
HAVING c > 5;

这个查询按照员工姓名的长度进行分组，并计算每个长度组的员工个数，最后筛选出员工个数大于5的组。

6. 按多个字段分组

查询每个部门每个工种的员工平均工资：

SELECT AVG(salary), department_id, job_id  
FROM employees  
GROUP BY department_id, job_id;

这个查询同时按照 department_id 和 job_id 两个字段进行分组，并计算每个部门每个工种的员工平均工资。

注意事项

1. 分组函数做条件：分组函数（如SUM(), AVG(), MAX(), MIN(), COUNT()）做条件时，必须放在HAVING子句中，而不能放在WHERE子句中。
2. 分组列的限制：GROUP BY子句列出的每个列都必须是检索列或有效的表达式，但不能是聚合函数。
3. NULL值的处理：如果分组列中包含NULL值，则NULL将作为一个单独的分组返回。