目录

  1. GROUP BY 语句概述
  2. GROUP BY 基本语法
  3. 使用 GROUP BY 进行分组统计
  4. GROUP BY 与聚合函数
  5. GROUP BY 与 HAVING 过滤分组结果
  6. GROUP BY 多列分组
  7. GROUP BY 与 ORDER BY 配合使用
  8. GROUP BY 与 ROLLUP 扩展
  9. GROUP BY 语句的性能优化
  10. 参考资料

GROUP BY 语句概述

GROUP BY 语句用于对查询结果进行分组，通常与**聚合函数（SUM、COUNT、AVG、MAX、MIN 等）**一起使用，以便对每个分组进行计算。

例如，按 department 统计每个部门的员工数量：

SELECT department, COUNT(*) AS employee_count
FROM employees
GROUP BY department;

GROUP BY 基本语法

SELECT column1, column2, 聚合函数(column3)
FROM table_name
GROUP BY column1, column2;

规则：

  1. GROUP BY 的列必须出现在 SELECT 语句中（除非是聚合函数）。
  2. GROUP BY 必须放在 WHERE 之后，ORDER BY 之前。
  3. GROUP BY 可以按单列或多列分组。

使用 GROUP BY 进行分组统计

示例：统计每个部门的员工数量

SELECT department, COUNT(*) AS employee_count
FROM employees
GROUP BY department;

结果示例：

+------------+----------------+
| department | employee_count |
+------------+----------------+
| HR         | 5              |
| IT         | 8              |
| Sales      | 12             |
+------------+----------------+

GROUP BY 与聚合函数

常见聚合函数

聚合函数作用
COUNT(*)统计每组的行数
SUM(column)计算每组的总和
AVG(column)计算每组的平均值
MAX(column)计算每组的最大值
MIN(column)计算每组的最小值

示例：计算每个部门的平均工资

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

结果示例：

+------------+------------+
| department | avg_salary |
+------------+------------+
| HR         | 5000.00    |
| IT         | 7200.50    |
| Sales      | 6300.75    |
+------------+------------+

GROUP BY 与 HAVING 过滤分组结果

HAVING 语句用于筛选分组后的数据，类似于 WHERE，但 WHERE 不能用于聚合函数。

示例：筛选员工数大于 5 的部门

SELECT department, COUNT(*) AS employee_count
FROM employees
GROUP BY department
HAVING COUNT(*) > 5;

结果示例：

+------------+----------------+
| department | employee_count |
+------------+----------------+
| IT         | 8              |
| Sales      | 12             |
+------------+----------------+

GROUP BY 多列分组

可以使用多个列进行分组，使分组更精细。

示例：按部门和职位统计员工数量

SELECT department, job_title, COUNT(*) AS employee_count
FROM employees
GROUP BY department, job_title;

结果示例：

+------------+------------+----------------+
| department | job_title  | employee_count |
+------------+------------+----------------+
| HR         | Manager    | 2              |
| HR         | Assistant  | 3              |
| IT         | Developer  | 5              |
| IT         | Analyst    | 3              |
+------------+------------+----------------+

GROUP BY 与 ORDER BY 配合使用

默认情况下，GROUP BY 不会对结果排序，但可以使用 ORDER BY 进行排序。

示例：按部门统计员工数量，并按员工数量降序排列

SELECT department, COUNT(*) AS employee_count
FROM employees
GROUP BY department
ORDER BY employee_count DESC;

结果示例：

+------------+----------------+
| department | employee_count |
+------------+----------------+
| Sales      | 12             |
| IT         | 8              |
| HR         | 5              |
+------------+----------------+

GROUP BY 与 ROLLUP 扩展

ROLLUP 用于计算小计和总计，适用于分组汇总数据。

示例：统计每个部门的员工数量，并计算总数

SELECT department, COUNT(*) AS employee_count
FROM employees
GROUP BY department WITH ROLLUP;

结果示例：

+------------+----------------+
| department | employee_count |
+------------+----------------+
| HR         | 5              |
| IT         | 8              |
| Sales      | 12             |
| NULL       | 25             |  -- 总计
+------------+----------------+

解释：

  • NULL 行表示所有部门的总员工数。

GROUP BY 语句的性能优化

  1. 使用索引加速 GROUP BY
    • GROUP BY 列应创建索引，提高查询性能。
    CREATE INDEX idx_department ON employees(department);
  2. *避免使用 SELECT （减少数据读取量）
    ❌ 不推荐： SELECT * FROM employees GROUP BY department; ✅ 推荐： SELECT department, COUNT(*) FROM employees GROUP BY department;
  3. 使用 WHERE 过滤数据，减少计算量
    • 先过滤数据再分组，提高效率。
    SELECT department, COUNT(*) FROM employees WHERE salary > 3000 GROUP BY department;
  4. 使用临时表缓存计算结果
    • 如果 GROUP BY 查询结果经常使用，可以存入临时表，提高查询速度。
    CREATE TEMPORARY TABLE temp_result AS SELECT department, COUNT(*) AS employee_count FROM employees GROUP BY department;

参考资料

如果你需要更多关于 GROUP BY 的高级优化方法或示例，欢迎继续交流！ 🚀