【重学 MySQL】三十八、group by的使用

GROUP BY 是 SQL 中一个非常重要的子句，它通常与聚合函数（如 COUNT(), MAX(), MIN(), SUM(), AVG() 等）一起使用，用于将多个记录组合成汇总行。GROUP BY 通过一个或多个列对结果集进行分组，并可以对每个分组应用聚合函数。

基本语法

SELECT column_name(s), AGGREGATE_FUNCTION(column_name)
FROM table_name
WHERE condition
GROUP BY column_name(s)
ORDER BY column_name(s);

• column_name(s): 你想要选择的列。
• AGGREGATE_FUNCTION(column_name): 应用于 column_name 的聚合函数。
• table_name: 你要从中选择数据的表。
• condition: 用于选择记录的条件（可选）。
• GROUP BY column_name(s): 指定根据哪些列来分组结果。
• ORDER BY column_name(s): 可选，用于对结果进行排序。

示例

假设我们有一个名为 employees 的表，它包含以下列：id, name, department, 和 salary。

示例 1: 计算每个部门的员工数

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

这个查询会返回每个部门以及该部门的员工数。

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

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

这会显示每个部门的平均工资。

示例 3: 结合 WHERE 子句

如果你只想查看某些部门的统计信息，你可以结合使用 WHERE 子句：

SELECT department, COUNT(*) AS num_employees
FROM employees
WHERE salary > 5000
GROUP BY department;

这个查询会返回工资超过 5000 的员工所在的部门以及每个部门的员工数。

WITH ROLLUP

MySQL中的GROUP BY ... WITH ROLLUP是一个强大的功能，它允许你执行聚合查询，并在结果集的末尾自动添加更高级别的汇总行。这些汇总行是通过对前面分组的结果进行进一步聚合得到的，从而提供了一种方便的方式来查看数据的不同层次的总计。

基本用法

当你使用GROUP BY ... WITH ROLLUP时，MySQL会生成一个包含所有指定列的分组结果集，并在结果集的末尾添加一个或多个汇总行。这些汇总行按照你在GROUP BY子句中指定的列的顺序进行汇总。对于每个汇总级别，它都会移除前面的列，并对剩余的列进行汇总。

示例

假设你有一个名为sales的表，其中包含year、product和amount三个字段，分别代表销售年份、产品名称和销售金额。

CREATE TABLE sales (year INT,product VARCHAR(50),amount DECIMAL(10, 2)
);INSERT INTO sales (year, product, amount) VALUES
(2020, 'Product A', 100.00),
(2020, 'Product B', 200.00),
(2021, 'Product A', 150.00),
(2021, 'Product B', 250.00);

如果你想按年份和产品分组查看销售金额，并在最后添加按年份的总计和所有记录的总计，你可以这样做：

SELECT year, product, SUM(amount) AS total_sales
FROM sales
GROUP BY year, product WITH ROLLUP;

这将生成如下结果（结果顺序可能略有不同，取决于MySQL的实现）：

+------+-----------+-------------+
| year | product   | total_sales |
+------+-----------+-------------+
| 2020 | Product A | 100.00      |
| 2020 | Product B | 200.00      |
| 2020 | NULL      | 300.00      |  -- 2020年总计
| 2021 | Product A | 150.00      |
| 2021 | Product B | 250.00      |
| 2021 | NULL      | 400.00      |  -- 2021年总计
| NULL | NULL      | 700.00      |  -- 所有记录总计
+------+-----------+-------------+

在这个例子中，WITH ROLLUP首先按year和product分组计算每个产品的销售总额，然后在每个年份的末尾添加该年份的总计（此时product列为NULL），最后在结果集的末尾添加所有记录的总计（此时year和product列均为NULL）。

注意事项

• 使用WITH ROLLUP时，应确保你的SQL查询可以处理NULL值，因为汇总行会包含NULL值。
• WITH ROLLUP可以与其他聚合函数（如COUNT(), AVG(), MIN(), MAX()等）一起使用。
• 在结果集中，你可能需要使用COALESCE或IFNULL等函数来处理或忽略NULL值，特别是在进行进一步的计算或展示时。

注意事项

• SELECT 中出现的非组函数的字段必须声明在 GROUP BY 中，反之，GROUP BY 中声明的字段可以不出现在 SELECT 中。
• GROUP BY 必须声明在 FROM 后面、WHERE 后面、ORDER BY 前面、LIMIT 前面
• 当使用 WITH ROLLUP 时，不能同时使用 ORDER BY 子句进行结果排序，即 WITH ROLLUP 和 ORDER BY 是互相排斥的
• 在 SELECT 列表中，除了聚合函数外，只能包含 GROUP BY 子句中指定的列。
• 如果在 SELECT 列表中包含了非聚合列且这些列未出现在 GROUP BY 子句中，那么查询将不会执行，并会报错（在某些数据库系统中，如 MySQL 的旧版本，这可能会静默地工作，但返回的结果可能不是你所期望的）。
• 聚合函数用于对 GROUP BY 子句产生的每个分组执行计算。
• 可以通过 HAVING 子句对分组后的结果进行过滤，这与 WHERE 子句不同，WHERE 在数据分组前进行过滤。