mysql - 创建一个查询以获取每个用户的最后 5 行

Question

通过此查询，我获得了创建网站的最后用户的所有通知:

    SELECT id,lasts.user_id FROM notifies INNER JOIN 
(SELECT user_id, MAX( created_at ) as last_at
FROM  `notifies` 
GROUP BY user_id
ORDER BY last_at DESC
LIMIT 5) as lasts
ON notifies.user_id = lasts.user_id

我只想获取这些用户的最后 5 条通知。我如何修改这个查询？

谢谢

Answer 1

SELECT  n.*
FROM    (
        SELECT  u.id,
                COALESCE(
                (
                SELECT  created_at
                FROM    notifies ni
                WHERE   ni.user_id = u.id
                ORDER BY
                        user_id DESC, created_at DESC
                LIMIT 5
                ), CAST('0001-01-01' AS DATE)) AS lt
        FROM    users u
        ) u
JOIN    notifies n
ON      n.user_id <= u.id
        AND n.user_id >= u.id
        AND n.created_at >= u.lt

在notifies (user_id, created_at)上创建索引使其快速发挥作用。

请注意n.user_id <= u.id AND n.user_id >= u.id而不仅仅是n.user_id = u.id应该用来制作 MySQL有效地使用该索引。