【MySQL】提高篇—复杂查询：使用GROUP BY与HAVING进行聚合查询_group by 和 having. having 子句在 group by 之后进行测试。您可以测-CSDN博客

本文链接：https://blog.csdn.net/thinking_chou/article/details/143028158

为了从表中提取有用的信息，常常需要对数据进行汇总和分析。聚合查询是一种常用的查询方式，能够从大量数据中提取出有意义的统计信息。

GROUP BY 子句用于将查询结果按照一个或多个列进行分组，并且通常与聚合函数（如 COUNT、SUM、AVG、MAX、MIN 等）一起使用，以计算每个组的汇总值。

HAVING 子句用于对分组后的结果进行过滤，通常在 GROUP BY 之后使用。它允许我们对聚合结果进行条件筛选，帮助我们提取出符合特定条件的分组数据。

在实际应用中，GROUP BY 和 HAVING 的组合非常重要。例如，在销售分析中，我们可能需要计算每个产品的总销售额，并找出销售额超过某个阈值的产品。通过使用 GROUP BY 和 HAVING，我们可以方便地实现这些需求。

1. 创建示例表

我们将创建两个表：products 和 sales，用于存储产品信息和销售记录。

-- 创建 products 表
CREATE TABLE products (
    product_id INT PRIMARY KEY AUTO_INCREMENT,
    product_name VARCHAR(100) NOT NULL,
    price DECIMAL(10, 2) NOT NULL
);

-- 创建 sales 表
CREATE TABLE sales (
    sale_id INT PRIMARY KEY AUTO_INCREMENT,
    product_id INT,
    quantity INT,
    sale_date DATE,
    FOREIGN KEY (product_id) REFERENCES products(product_id)
);

2. 插入示例数据

我们将插入一些示例数据到这两个表中，以便进行聚合查询。

-- 插入产品数据
INSERT INTO products (product_name, price) VALUES
('Laptop', 1000.00),
('Mouse', 25.00),
('Keyboard', 50.00),
('Monitor', 300.00),
('Tablet', 400.00);

-- 插入销售数据
INSERT INTO sales (product_id, quantity, sale_date) VALUES
(1, 10, '2024-01-10'),
(2, 50, '2024-01-12'),
(3, 30, '2024-01-15'),
(1, 5, '2024-01-20'),
(4, 20, '2024-01-22'),
(5, 15, '2024-01-25'),
(2, 20, '2024-01-28'),
(3, 10, '2024-01-30');

3. 使用 GROUP BY 进行聚合查询

示例 1：计算每个产品的总销售量

我们想要计算每个产品的总销售量。

-- 查询每个产品的总销售量
SELECT p.product_name, SUM(s.quantity) AS total_quantity
FROM products p
JOIN sales s ON p.product_id = s.product_id
GROUP BY p.product_name;

解释：

SELECT p.product_name, SUM(s.quantity) AS total_quantity：选择产品名称和销售数量的总和。
FROM products p JOIN sales s ON p.product_id = s.product_id：连接 products 表和 sales 表，使用产品 ID 作为连接条件。
GROUP BY p.product_name：按产品名称分组，计算每个产品的总销售量。

查询结果：

+--------------+----------------+
| product_name | total_quantity  |
+--------------+----------------+
| Keyboard     |             40 |
| Laptop       |             15 |
| Monitor      |             20 |
| Mouse        |             70 |
| Tablet       |             15 |
+--------------+----------------+

4. 使用 HAVING 进行过滤

示例 2：查找总销售量超过 20 的产品

我们希望找出总销售量超过 20 的产品。

-- 查询总销售量超过 20 的产品
SELECT p.product_name, SUM(s.quantity) AS total_quantity
FROM products p
JOIN sales s ON p.product_id = s.product_id
GROUP BY p.product_name
HAVING total_quantity > 20;

解释：

HAVING total_quantity > 20：在分组后对总销售量进行过滤，返回总销售量超过 20 的产品。

查询结果：

+--------------+----------------+
| product_name | total_quantity  |
+--------------+----------------+
| Keyboard     |             40 |
| Mouse        |             70 |
| Laptop       |             15 |
| Monitor      |             20 |
+--------------+----------------+

5. 结合多个聚合函数

示例 3：计算每个产品的总销售额和平均销售量

我们可以同时计算每个产品的总销售额和平均销售量。

-- 查询每个产品的总销售额和平均销售量
SELECT p.product_name,
       SUM(s.quantity * p.price) AS total_sales,
       AVG(s.quantity) AS average_quantity
FROM products p
JOIN sales s ON p.product_id = s.product_id
GROUP BY p.product_name;

解释：

SUM(s.quantity * p.price) AS total_sales：计算每个产品的总销售额（销售数量乘以单价）。
AVG(s.quantity) AS average_quantity：计算每个产品的平均销售量。

查询结果：

+--------------+--------------+------------------+
| product_name | total_sales  | average_quantity  |
+--------------+--------------+------------------+
| Keyboard     |        2000.00 |              40  |
| Laptop       |       15000.00 |              15  |
| Monitor      |        6000.00 |              20  |
| Mouse        |        1750.00 |              70  |
| Tablet       |        6000.00 |              15  |
+--------------+--------------+------------------+