自学内容网 自学内容网

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 会在 FROMWHERE 语句之后,HAVING, SELECT, DISTINCT, ORDER BYLIMIT 子句之前执行 GROUP BY :
在这里插入图片描述

单独使用 GROUP BY

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

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 后直接跟 ASCDESC (这不是标准SQL的做法,且因MySQL版本而异),更标准的做法是在整个查询的末尾使用 ORDER BY

HAVING 过滤分组

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

语法结构:

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

MySQL 会在 FROM, WHERE, SELECTGROUP BY 语句之后,ORDER BYLIMIT 语句之前执行 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_idjob_id 两个字段进行分组,并计算每个部门每个工种的员工平均工资。

注意事项

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

原文地址:https://blog.csdn.net/Zachyy/article/details/140834449

免责声明:本站文章内容转载自网络资源,如本站内容侵犯了原著者的合法权益,可联系本站删除。更多内容请关注自学内容网(zxcms.com)!