SQL合并表查询：获取酒店客人各房间最新停留记录

你的现有SQL只是完成了表关联和时间排序，但没有对每个房间的重复记录做去重处理，所以Bob在Spa的两条记录都被返回了。要实现每个房间仅显示最新停留记录的需求，这里提供两种实用的解决方案：

方法1：使用窗口函数（推荐，简洁高效） #

利用ROW_NUMBER()窗口函数，按房间分组后给每组内的记录按时间倒序编号，最后只保留编号为1的那条（也就是每个房间的最新记录）：

SELECT guest_id, name, room_name, last_updated
FROM (
    SELECT 
        guests.guest_id, 
        guests.name, 
        rooms.room_name, 
        room_guest_is_in.last_updated,
        -- 按房间分组，给组内记录按时间倒序分配序号
        ROW_NUMBER() OVER (PARTITION BY room_guest_is_in.room_id ORDER BY room_guest_is_in.last_updated DESC) AS record_rank
    FROM guests 
    INNER JOIN room_guest_is_in ON guests.guest_id = room_guest_is_in.guest_id 
    INNER JOIN rooms ON room_guest_is_in.room_id = rooms.room_id 
    WHERE guests.guest_id = 1
) AS ranked_records
WHERE record_rank = 1 -- 仅保留每个房间的第一条（最新）记录
ORDER BY last_updated DESC;

方法2：子查询筛选每个房间的最新时间 #

先通过子查询找出Bob在每个房间的最新停留时间，再关联回原表获取完整的记录信息：

SELECT 
    g.guest_id, 
    g.name, 
    r.room_name, 
    rgi.last_updated
FROM guests g
INNER JOIN room_guest_is_in rgi ON g.guest_id = rgi.guest_id
INNER JOIN rooms r ON rgi.room_id = r.room_id
WHERE 
    g.guest_id = 1
    AND (rgi.guest_id, rgi.room_id, rgi.last_updated) IN (
        -- 先获取Bob每个房间的最新停留时间
        SELECT guest_id, room_id, MAX(last_updated)
        FROM room_guest_is_in
        WHERE guest_id = 1
        GROUP BY guest_id, room_id
    )
ORDER BY rgi.last_updated DESC;

原SQL问题说明 #

你的原SQL仅做了表连接和排序，没有对同一房间的多条记录做过滤。Bob在Spa有两个不同时间的停留记录，因此都会被查询返回。上面两种方法都能精准过滤掉每个房间中时间较早的记录，最终得到你期望的结果：

guest_id | name | room_name | last_updated
1 | Bob | Restaurant | 2019-03-18 12:11:36
1 | Bob | Spa | 2019-03-18 11:19:34

内容的提问来源于stack exchange，提问作者Yuri