IT评测·应用市场-qidao123.com

标题: MySQL：GROUP BY 分组查询 [打印本页]

---

作者: 半亩花草    时间: 2024-8-28 18:09
标题: MySQL：GROUP BY 分组查询
分组查询是SQL中一个非常强大的功能，它允许我们将数据按照一个或多个字段举行分组，并对每个分组举行聚合计算（如求和、平均值、最大值、最小值等）。在MySQL中，我们使用 GROUP BY 关键字来实现分组查询。
核心语法

1. SELECT column_1, column_2, ..., AGGREGATE_FUNCTION(column_N)  
2. FROM table_name  
3. WHERE condition  
4. GROUP BY column_1, column_2, ...  
5. HAVING condition  
6. 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() 函数会把每个分组的字段值都显示出来。

1. SELECT sex, GROUP_CONCAT(name)
2. FROM student
3. GROUP BY sex;
4. '''
5. +------+----------------------------+
6. | sex  | GROUP_CONCAT(name)         |
7. +------+----------------------------+
8. | 女   | Henry,Jim,John,Thomas,Tom  |
9. | 男   | Dany,Green,Jane,Lily,Susan |
10. +------+----------------------------+
11. '''

复制代码

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

1. SELECT age,sex GROUP_CONCAT(name)
2. FROM student
3. GROUP BY age,sex;
4. '''
5. +------+------+--------------------+
6. | age  | sex  | GROUP_CONCAT(name) |
7. +------+------+--------------------+
8. |   21 | 女   | John               |
9. |   22 | 女   | Thomas             |
10. |   22 | 男   | Jane,Lily          |
11. |   23 | 女   | Henry,Tom          |
12. |   23 | 男   | Green,Susan        |
13. |   24 | 女   | Jim                |
14. |   25 | 男   | Dany               |
15. +------+------+--------------------+
16. '''

复制代码

如果第一个字段中的数据都是唯一的，那么 MySQL将不再对第二个字段举行分组。
GROUP 和 聚合函数 aggregate_function()

在数据统计时，GROUP BY 关键字经常和 聚合函数 一起使用。
聚合函数包括 COUNT()，SUM()，AVG()，MAX() 和 MIN()。

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

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

复制代码

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

1. SELECT department_id, AVG(salary) AS avg_salary  
2. FROM employees  
3. GROUP BY department_id;

复制代码

GROUP BY 和 表达式

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

1. SELECT expression FROM table_name
2. GROUP BY expression;

复制代码

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

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

1. SELECT YEAR(hire_date) AS hire_year, COUNT(*) AS hires_per_year  
2. FROM employees  
3. 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 一同使用。
语法结构：

1. SELECT column_ FROM table_name
2. [ORDER BY column_]
3. 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 根据前面已经查询出的字段举行过滤。
  

1. SELECT name, sex FROM student WHERE height>180;
2. '''可以正常输出'''
4. SELECT name, sex FROM student HAVING height>180;
5. '''
6. 报错
7. ERROR 1054 (42S22): Unknown column 'height' in 'having clause'
8. '''

复制代码

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

分组查询案例

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

1. SELECT AVG(salary), department_id  
2. FROM employees  
3. GROUP BY department_id;

复制代码

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

1. SELECT MAX(salary), job_id  
2. FROM employees  
3. GROUP BY job_id;

复制代码

这个查询将employees表中的数据按照job_id分组，并计算每个工种的最高工资。
3. 添加筛选条件

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

1. SELECT AVG(salary), department_id  
2. FROM employees  
3. WHERE email LIKE '%a%'  
4. GROUP BY department_id;

复制代码

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

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

1. SELECT COUNT(*), department_id  
2. FROM employees  
3. GROUP BY department_id  
4. HAVING COUNT(*) > 2;

复制代码

这个查询起首按照 department_id 分组，然后计算每个部门的员工个数，最后通过HAVING子句筛选出员工个数大于2的部门。
5. 按表达式或函数分组

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

1. SELECT COUNT(*) AS c, LENGTH(last_name) AS len_name  
2. FROM employees  
3. GROUP BY LENGTH(last_name)  
4. HAVING c > 5;

复制代码

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

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

1. SELECT AVG(salary), department_id, job_id  
2. FROM employees  
3. GROUP BY department_id, job_id;

复制代码

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

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

免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！更多信息从访问主页：qidao123.com:ToB企服之家，中国第一个企服评测及商务社交产业平台。

---

欢迎光临 IT评测·应用市场-qidao123.com (https://dis.qidao123.com/)

Powered by Discuz! X3.4