MySQL: 实现 "ORDER BY" 后 "GROUP BY" 取第一行的 ID
在数据库开发中，我们经常需要对数据进行过滤、排序和分组。在本篇文章中，我们将学习如何在 MySQL 中实现“ORDER BY”后“GROUP BY”，并从每个分组中提取第一行的 ID。我们会通过一个实用的示例来展示整个流程。
1. 业务需求
假设我们有一个名为 orders 的表，包含以下字段：
id: 订单的唯一标识符customer_id: 客户唯一标识符order_date: 订单日期amount: 订单金额
我们的目标是针对每个 customer_id，仅选择最新的订单（根据 order_date 排序），并返回这些订单的 ID。
2. 流程概述
以下是实现这个功能的流程步骤：
| 步骤 | 描述 |
|---|---|
| 1 | 查询最新的订单，按客户分组 |
| 2 | 将查询结果按日期降序排序 |
| 3 | 提取每个客户分组的第一行 |
| 4 | 最终返回所需的 ID |
流程图
使用以下的 Mermaid 语法可以表示上述流程:
flowchart TD
A[开始] --> B[查询最新的订单]
B --> C[按客户分组]
C --> D[按日期降序排序]
D --> E[提取每个客户的第一行]
E --> F[返回所需的 ID]
F --> G[结束]
3. 实现步骤
步骤 1: 查询最新订单
我们首先要查询所有的订单，为后续的分组和排序做准备。
SELECT id, customer_id, order_date, amount
FROM orders;
这条 SQL 语句会从
orders表中提取所有的订单记录。
步骤 2: 按客户分组并排序
在这个步骤中，我们将数据按 customer_id 分组，并根据 order_date 进行排序。我们可以使用 ROW_NUMBER() 函数来实现这一点。
SELECT id, customer_id, order_date,
ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY order_date DESC) as row_num
FROM orders;
这条 SQL 语句通过在每个
customer_id的分组中按order_date降序排列，为每条记录分配一个排名（row_num）。最新的订单将获得排名 1。
步骤 3: 提取每个客户的第一行
在这个步骤中，我们将使用一个临时表来筛选出每个客户最新的订单。
WITH ranked_orders AS (
SELECT id, customer_id, order_date,
ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY order_date DESC) as row_num
FROM orders
)
SELECT id
FROM ranked_orders
WHERE row_num = 1;
使用 CTE（公共表表达式）
ranked_orders来存储已排名的订单记录，最后我们从中选择出row_num等于 1 的最新订单 ID。
4. 运行与验证
执行上面的 SQL 语句后，你将能获得每个客户最新订单的 ID。为了确保结果的正确性，你可以在数据库中运行上述 SQL，并验证结果的准确性。
4. 数据库表结构 (orders 表)
为了帮助小白理解，我们使用关系图描述 orders 表的结构：
erDiagram
orders {
int id PK "订单ID"
int customer_id "客户ID"
date order_date "订单日期"
decimal amount "订单金额"
}
这个关系图展示了
orders表的字段和属性。有助于更好地理解我们要处理的数据结构。
总结
通过上述的步骤，我们详细介绍了如何实现“ORDER BY”后“GROUP BY”从每个分组中提取最新的一行 ID。我们使用了 SQL 的窗口函数 ROW_NUMBER() 来对数据进行分组和排序，并通过一个公共表表达式（CTE）来获取最终结果。
对于刚入行的小白，这种技术实现不仅提升了团队数据库的查询效率，也增强了对 SQL 的理解和掌握。希望你能将这些方法应用到实际工作中，并逐渐丰富你的数据库操作技能。
