通过此查询,我将获得创建它们的网站的最后一个用户的所有通知:
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次通知。
如何修改此查询?
谢谢
最佳答案
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
有效地使用此索引。