MySQL LEFT JOIN 只取被关联表中一条数据

前言

最近遇到一个需求，需要查询被关联表中最新的一条数据，在此记录一下。

创建表和初始化数据

比如我有两个表，用户表（user）、用户登录记录表(user_login_log)。现在需要查询用户并同时带出他最近的登录记录。

下面是初始化SQL

CREATE TABLE `user` (
	`id` INT (11) NOT NULL AUTO_INCREMENT,
	`name` VARCHAR (50) DEFAULT NULL COMMENT '姓名',
	PRIMARY KEY (`id`)
);

CREATE TABLE `user_login_log` (
	`id` INT (11) NOT NULL AUTO_INCREMENT,
	`user_id` INT (11) NOT NULL,
	`login_time` TIMESTAMP NULL COMMENT '登录时间',
	PRIMARY KEY (`id`)
);

-- 初始化数据
INSERT INTO `user` (`id`, `name`)
VALUES
	(1, 'zhangsan'),
	(2, 'lisi');

INSERT INTO `user_login_log` (`id`,`user_id`,`login_time`) VALUES
	(1,'1','2020-01-01 12:58:30'),
	(2,'1','2020-01-02 12:58:30'),
	(3,'1','2020-01-03 12:58:30');

使用GROUP BY

首先想到的就是GROUP BY ，看下面SQL

SELECT 	* FROM `user` u
LEFT JOIN (
	SELECT MAX(login_time), id AS log_id,user_id
	FROM `user_login_log`
	GROUP BY user_id
) l ON u.id = l.user_id;

下面是执行结果：

初一看好像结果是对的，再仔细看log_id字段，按照想法应该是最后一条记录的ID(3)才对呀 [捂脸]。

查了一下资料，发现MySQL的GROUP BY 默认会保留“最先搜索出来的一条数据”
这样子的话，我又对SQL进行了改造：

SELECT 	* FROM `user` u
LEFT JOIN (
	SELECT * FROM (SELECT *
	FROM `user_login_log`
	ORDER BY login_time DESC)temp GROUP BY temp.user_id
) l ON u.id = l.user_id;

一执行，结果和刚才一模一样。[-_-||]

再查询了资料：根据MySQL手册，在MySQL5.7中，如果不加LIMIT，系统会把ORDER BY优化掉(MySQL5.5或之前不会)。再修改SQL：

SELECT 	* FROM `user` u
LEFT JOIN (
	SELECT * FROM (SELECT *
	FROM `user_login_log`
	ORDER BY login_time DESC LIMIT 10000)temp GROUP BY temp.user_id
) l ON u.id = l.user_id;

这样子终于行了：

但是看着那个LIMIT 10000 很不爽，还有没有其他的方式呢，下面我们看看另外的方式。

其他的方式

在我不断的查询资料下，在StackOverflow看到了另外一种方案：

SELECT 	* FROM `user` u
LEFT JOIN `user_login_log` l ON l.id=(
	SELECT id FROM `user_login_log` WHERE u.id=user_Id
	ORDER BY login_time DESC  LIMIT 1);

执行了一下也符合预期。

OK，这个问题就先到这里了。