mysql - 选择左连接上的最后一条记录 mysql

Question

我有一个存储所有用户连接(日期和IP)的表，我想通过单个查询检索所有用户数据(昵称、头像...)+该用户的连接历史记录表的最后一条记录...

SELECT 
    *
FROM
    `users`
        LEFT JOIN
    `connections_history` ON `users`.`id` = `connections_history`.`guid`

我该如何继续，谢谢

Answer 1

假设connections_history表有一个AUTO_INCRMENT列id:

SELECT *
FROM (
    SELECT u.*, MAX(h.id) as hid
    FROM users u
    LEFT JOIN connections_history h ON u.id = h.guid
    GROUP BY u.id
) u
LEFT JOIN connections_history h ON h.id = u.hid