我有2张桌子：照片等.我试图在一天内根据喜欢的数量获得流行的照片.所以基本上就像’今天流行’.
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,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(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