mysql - MySQL:24小时间隔加入

我有以下查询：

select * from winners
join profiles on winners.subscriber_id = profiles.subscriber_id
join comments on comments.profile_id = profiles.vanity
join videos on winners.subscriber_id = videos.subscriber_id
join photos on winners.subscriber_id = photos.subscriber_id
where winners.round_id >= 4 AND winners.active = true
AND (comments.created_at > DATE_SUB( NOW(), INTERVAL 24 HOUR) OR videos.created_at > DATE_SUB( NOW(), INTERVAL 24 HOUR) OR photos.created_at > DATE_SUB( NOW(), INTERVAL 24 HOUR)) AND comments.parent_id = 0;

此查询在Cron作业上运行，该作业将为每个获奖者获取过去24小时的最新信息，然后向每个获奖者粉丝发送最新信息。

我遇到的问题是该查询回撤了误报，因为如果在过去24小时内对视频进行了评论，则它会回撤视频和评论。是否有限制来获取过去24小时内发生的一切？

最佳答案

我会将24小时检查移至加入。这样，我们仅加入最近更新的项目。

select * from winners
    join profiles on winners.subscriber_id = profiles.subscriber_id
    left join comments on (comments.profile_id = profiles.vanity AND comments.created_at > DATE_SUB( NOW(), INTERVAL 24 HOUR))
    left join videos on (winners.subscriber_id = videos.subscriber_id AND videos.created_at > DATE_SUB( NOW(), INTERVAL 24 HOUR))
    left join photos on (winners.subscriber_id = photos.subscriber_id AND photos.created_at > DATE_SUB( NOW(), INTERVAL 24 HOUR))
where winners.round_id >= 4 AND winners.active = true
AND comments.parent_id = 0;

关于mysql - MySQL:24小时间隔加入，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/6914292/