mysql left join 一对多取最新一条

原创

mob64ca12e60047 2023-11-02 15:03:29 ©著作权

文章标签 连接查询一对多子查询 文章分类 MySQL 数据库

©著作权归作者所有：来自51CTO博客作者mob64ca12e60047的原创作品，请联系作者获取转载授权，否则将追究法律责任

MySQL Left Join一对多取最新一条的实现方法

概述

在实际的数据库应用中，经常会遇到需要使用LEFT JOIN关键字进行连接查询，并且需要取出一对多关系中最新的一条数据。本文将介绍如何使用MySQL实现这一需求。

问题描述

假设有两个表：users和orders，它们之间的关系是一对多的关系。现在需要查询所有用户以及他们最新一条订单的信息。

users表结构

Field	Type	Null	Key	Extra
id	int(11)	NO	PRI	自增主键
name	varchar(100)	NO		用户名

orders表结构

Field	Type	Null	Key	Extra
id	int(11)	NO	PRI	自增主键
user_id	int(11)	NO	MUL	关联用户ID
amount	decimal(10,2)	NO		订单金额
created_at	datetime	NO		创建时间

解决方案

解决这个问题的思路如下：

使用LEFT JOIN关键字连接查询users和orders表，以获取所有用户及其对应的订单信息；
使用子查询获取每个用户的最新一条订单；
将子查询的结果作为临时表，再次与users表进行连接查询，以获取最终的结果。

下面是具体的实现步骤和代码示例。

步骤一：连接查询

首先，我们使用LEFT JOIN关键字连接查询users和orders表，获取所有用户以及他们的订单信息。代码如下所示：

SELECT u.id, u.name, o.id AS order_id, o.amount, o.created_at
FROM users AS u
LEFT JOIN orders AS o ON u.id = o.user_id;

这段代码会返回一个结果集，其中包含了所有用户和他们的订单信息。

步骤二：获取每个用户的最新订单

接下来，我们需要使用子查询获取每个用户的最新一条订单。代码如下所示：

SELECT user_id, MAX(created_at) AS max_created_at
FROM orders
GROUP BY user_id;

这段代码会返回一个临时表，其中包含了每个用户的ID和最新订单的创建时间。

步骤三：连接查询获取最新订单信息

最后，我们将第二步中得到的临时表与users表进行连接查询，以获取每个用户的最新一条订单信息。代码如下所示：

SELECT u.id, u.name, o.id AS order_id, o.amount, o.created_at
FROM users AS u
LEFT JOIN orders AS o ON u.id = o.user_id
JOIN (
    SELECT user_id, MAX(created_at) AS max_created_at
    FROM orders
    GROUP BY user_id
) AS t ON o.user_id = t.user_id AND o.created_at = t.max_created_at;

这段代码会返回一个结果集，其中包含了所有用户以及他们最新一条订单的信息。