MySQL - GROUP BY 子句

MySQL：GROUP BY 子句

GROUP BY 子句是什么？

MySQL 中的 GROUP BY 子句用于将指定列中具有相同值的行分组为汇总行。它是数据分析和报告的重要工具，允许您对数据子集执行聚合计算。

例如，在销售表中，您可以按 product_category 分组以查找每个类别的总销售额。GROUP BY 几乎总是与聚合函数（如 COUNT()、SUM()、AVG()、MIN() 和 MAX()）结合使用，以生成有意义的汇总信息。

GROUP BY 子句位于 WHERE 子句之后，HAVING 或 ORDER BY 子句之前。

通用语法

SELECT
    column_name1,
    aggregate_function(column_name2)
FROM
    table_name
WHERE
    condition
GROUP BY
    column_name1
HAVING
    aggregate_condition
ORDER BY
    column_name1;

示例设置

我们将在示例中使用以下 CUSTOMERS 表：

CREATE TABLE CUSTOMERS(
    ID INT PRIMARY KEY AUTO_INCREMENT,
    NAME VARCHAR(100) NOT NULL,
    AGE INT NOT NULL,
    ADDRESS VARCHAR(255),
    SALARY DECIMAL(10, 2)
);

INSERT INTO CUSTOMERS (NAME, AGE, ADDRESS, SALARY) VALUES
('Ramesh', 32, 'Ahmedabad', 2000.00),
('Khilan', 25, 'Delhi', 1500.00),
('Kaushik', 23, 'Kota', 2000.00),
('Chaitali', 25, 'Mumbai', 6500.00),
('Hardik', 27, 'Bhopal', 8500.00),
('Komal', 22, 'Hyderabad', 4500.00),
('Muffy', 24, 'Indore', 10000.00);

在单个列上使用 GROUP BY 和聚合函数

GROUP BY 最常见的用法是按单个列分组并应用聚合函数。

示例：按年龄统计客户数量

让我们找出每个年龄组有多少客户。

SELECT
    AGE,
    COUNT(*) AS NumberOfCustomers
FROM
    CUSTOMERS
GROUP BY
    AGE;

输出

AGE	客户数量
32	1
25	2
23	1
27	1
22	1
24	1

示例：按年龄计算平均薪资

现在让我们找出每个年龄组的平均薪资。

SELECT
    AGE,
    AVG(SALARY) AS AverageSalary
FROM
    CUSTOMERS
GROUP BY
    AGE;

输出

AGE	平均薪资
32	2000.00
25	4000.00
23	2000.00
27	8500.00
22	4500.00
24	10000.00

在多个列上使用 GROUP BY

您还可以按多个列进行分组以创建更细粒度的汇总。数据库将为指定列中值的每种组合创建一个唯一的分组。

示例：按地址和年龄分组

为了说明，让我们添加一条重复记录并按 ADDRESS 和 AGE 分组。

INSERT INTO CUSTOMERS (NAME, AGE, ADDRESS, SALARY) VALUES ('Riya', 25, 'Mumbai', 7000.00);

SELECT
    ADDRESS,
    AGE,
    COUNT(*) AS CountInGroup
FROM
    CUSTOMERS
GROUP BY
    ADDRESS, AGE;

结果将显示每个唯一的 (ADDRESS, AGE) 对的计数。例如，('Mumbai', 25) 现在将有 2 个计数。

使用 HAVING 子句过滤分组

WHERE 子句在行分组之前进行过滤。如果您想在分组之后，根据聚合函数的结果来过滤结果，则必须使用 HAVING 子句。

示例：查找客户数量多于一个的年龄组

SELECT
    AGE,
    COUNT(*) AS NumberOfCustomers
FROM
    CUSTOMERS
GROUP BY
    AGE
HAVING
    COUNT(*) > 1;

输出

AGE	客户数量
25	2

与 ORDER BY 的交互

您可以使用 ORDER BY 对最终分组结果进行排序。默认情况下，分组结果的顺序不保证。

SELECT
    AGE,
    AVG(SALARY) AS AverageSalary
FROM
    CUSTOMERS
GROUP BY
    AGE
ORDER BY
    AverageSalary DESC;

此查询将按平均薪资从高到低列出年龄组。

理解 ONLY_FULL_GROUP_BY

现代版本的 MySQL 默认启用 ONLY_FULL_GROUP_BY SQL 模式。这是一个关键概念。这意味着 SELECT 列表中任何不是聚合函数一部分的列必须包含在 GROUP BY 子句中。这可以防止结果出现歧义。

例如，在启用此模式的情况下，以下查询是无效的，因为 NAME 不在聚合函数中，也不在 GROUP BY 子句中。对于有两个人的 ‘25’ 年龄组，MySQL 应该显示哪个 NAME 呢？

-- 此查询在默认情况下会在现代 MySQL 中失败
SELECT NAME, AGE, COUNT(*) FROM CUSTOMERS GROUP BY AGE;

要在每个分组内获取名称列表，您可以使用 GROUP_CONCAT() 函数：

SELECT
    AGE,
    COUNT(*) AS NumberOfCustomers,
    GROUP_CONCAT(NAME) AS CustomerNames
FROM
    CUSTOMERS
GROUP BY
    AGE;