MySQL：GROUP BY 分组查询

MySQL：GROUP BY 分组查询

引用

CSDN

1.

https://blog.csdn.net/Zachyy/article/details/140834449

分组查询是SQL中一个非常强大的功能，它允许我们将数据按照一个或多个字段进行分组，并对每个分组进行聚合计算（如求和、平均值、最大值、最小值等）。在MySQL中，我们使用GROUP BY关键字来实现分组查询。本文将详细介绍GROUP BY的核心语法、执行顺序、与DISTINCT的区别、与聚合函数的使用、表达式分组、HAVING子句的使用等多个方面，并通过多个具体案例进行说明。

核心语法

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关键字时，查询结果会只显示每个分组的第一条记录，相当于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将作为一个单独的分组返回。