MySQL分组求和的完整解析|极客笔记

MySQL分组求和的完整解析

1. 简介

MySQL 是一种关系型数据库管理系统，它提供了丰富的功能和语法，用于操作和管理数据。在某些情况下，我们需要对数据库中的数据进行分组并求和。本文将详细介绍 MySQL 中分组求和的方法和示例。

2. 分组求和的语法

分组求和基于 SQL 的 GROUP BY 和 SUM 函数实现。下面是分组求和的语法：

SELECT 列名1, SUM(列名2)
FROM 表名
GROUP BY 列名1;

其中，列名1 是用于分组的列，列名2 是要求和的列，表名是要操作的数据表名。

3. 示例代码和运行结果

3.1. 创建测试数据表

我们将以一个名为 “orders” 的订单表为例进行演示。首先，我们需要创建一个包含 “order_id”、”customer_id” 和 “amount” 列的测试表：

CREATE TABLE orders (
  order_id INT PRIMARY KEY,
  customer_id INT,
  amount DECIMAL(10, 2)
);

INSERT INTO orders VALUES (1, 1, 10.00);
INSERT INTO orders VALUES (2, 1, 20.00);
INSERT INTO orders VALUES (3, 2, 15.00);
INSERT INTO orders VALUES (4, 3, 5.00);
INSERT INTO orders VALUES (5, 3, 10.00);

3.2. 分组求和示例

以下是几个使用分组求和的示例：

3.2.1. 求每个客户的订单总金额

SELECT customer_id, SUM(amount)
FROM orders
GROUP BY customer_id;

运行结果：

+-------------+-------------+
| customer_id | SUM(amount) |
+-------------+-------------+
|           1 |       30.00 |
|           2 |       15.00 |
|           3 |       15.00 |
+-------------+-------------+

3.2.2. 求每个客户的订单总数和总金额

SELECT customer_id, COUNT(order_id), SUM(amount)
FROM orders
GROUP BY customer_id;

运行结果：

+-------------+-----------------+-------------+
| customer_id | COUNT(order_id) | SUM(amount) |
+-------------+-----------------+-------------+
|           1 |               2 |       30.00 |
|           2 |               1 |       15.00 |
|           3 |               2 |       15.00 |
+-------------+-----------------+-------------+

3.2.3. 按客户分组，只显示订单总金额大于等于20的客户

SELECT customer_id, SUM(amount)
FROM orders
GROUP BY customer_id
HAVING SUM(amount) >= 20;

运行结果：

+-------------+-------------+
| customer_id | SUM(amount) |
+-------------+-------------+
|           1 |       30.00 |
+-------------+-------------+

3.2.4. 按客户和订单金额范围分组，求每个组的订单总数和总金额

SELECT customer_id, 
  CASE 
    WHEN amount < 10 THEN '小额订单'
    WHEN amount >= 10 AND amount < 20 THEN '中额订单'
    ELSE '大额订单'
  END AS amount_range,
  COUNT(order_id), 
  SUM(amount)
FROM orders
GROUP BY customer_id, amount_range;

运行结果：

+-------------+---------------+-----------------+-------------+
| customer_id | amount_range  | COUNT(order_id) | SUM(amount) |
+-------------+---------------+-----------------+-------------+
|           1 | 小额订单      |               0 |        0.00 |
|           1 | 中额订单      |               2 |       30.00 |
|           2 | 中额订单      |               1 |       15.00 |
|           3 | 小额订单      |               0 |        0.00 |
|           3 | 中额订单      |               2 |       15.00 |
+-------------+---------------+-----------------+-------------+

3.2.5. 按客户分组，只显示订单总数大于1的客户

SELECT customer_id, COUNT(order_id), SUM(amount)
FROM orders
GROUP BY customer_id
HAVING COUNT(order_id) > 1;

运行结果：

+-------------+-----------------+-------------+
| customer_id | COUNT(order_id) | SUM(amount) |
+-------------+-----------------+-------------+
|           1 |               2 |       30.00 |
|           3 |               2 |       15.00 |
+-------------+-----------------+-------------+