MySQL基于不同时间级别的热门行

我有 2 张桌子：photo and like。我正在尝试根据一天内的点赞数来获取热门照片。所以基本上是这样的“今天流行”.

SELECT
  p.id AS id, COUNT(li.id) AS total_likes \
FROM `photo` p \
  LEFT JOIN `like` li \
    ON p.id = li.photo_id \
WHERE
  li.date > DATE_SUB(CURDATE(), INTERVAL 1 DAY) \
GROUP BY \
  p.id

当今天有足够多的喜欢时，这种方法很有效。但如果过去一天没有点赞，则不会返回任何记录。

我也想稍微改变一下这一点。可以制作等级吗？例如：
根据多天对照片进行排名：

1. Get photos based on how many likes today
2. Get photos based on how many likes for last week
and so on...

基本上，假设我们需要获得 30 个项目。首先，它会尝试根据今天的点赞数来获取行。它可以是 20、15 等任意数字。然后它将获取总数达到 30 所需的剩余行，但现在将根据一周内的点赞数进行排序。

So something like:
SELECT FROM photo SORT BY likes today, likes in a week ...

感谢您的帮助！

您可以根据 CASE 计算不同的计数，例如过去 30 天的点赞数以及最后一天的计数。上周和过去 30 天：

SELECT
  p.id AS id
  ,COUNT(CASE WHEN li.DATE > DATE_SUB(CURDATE(), INTERVAL 1 DAY) THEN li.id END) AS daily_likes
  ,COUNT(CASE WHEN li.DATE > DATE_SUB(CURDATE(), INTERVAL 7 DAY) THEN li.id END) AS weekly_likes
  ,COUNT(li.id) AS total_likes
FROM `photo` p
JOIN `LIKE` li
    ON p.id = li.photo_id
WHERE
  li.DATE > DATE_SUB(CURDATE(), INTERVAL 30 DAY)
GROUP BY
  p.id
ORDER BY daily_likes DESC, weekly_likes DESC, total_likes DESC
LIMIT 30

我不知道你的限制是基于哪个定义，它可能是这样的

SELECT *
FROM
 (
   SELECT
     p.id AS id
     ,COUNT(CASE WHEN li.DATE > DATE_SUB(CURDATE(), INTERVAL 1 DAY) THEN li.id END) AS daily_likes
     ,COUNT(CASE WHEN li.DATE > DATE_SUB(CURDATE(), INTERVAL 7 DAY) THEN li.id END) AS weekly_likes
     ,COUNT(li.id) AS total_likes
   FROM `photo` p
   JOIN `LIKE` li
       ON p.id = li.photo_id
   WHERE
     li.DATE > DATE_SUB(CURDATE(), INTERVAL 30 DAY)
   GROUP BY
     p.id
  ) AS dt
ORDER BY
   case when daily_likes > 20 then daily_likes else 0 end desc,
   case when weekly_likes > 100 then weekly_likes else 0 end desc,
   total_likes DESC
LIMIT 30