I have two tables:
posts{id,post,tag_id,date开发者_高级运维}
tags{id,tag,date}
I want to retrieve the most popular tags used from the post table, used in the last 12 hours.
¿How can I achieve this?
Thanks.I think this should work:
SELECT tags.tag, count(*) AS count
FROM posts INNER JOIN tags
ON (posts.tag_id = tags.id)
WHERE posts.date >= SUBTIME(NOW(), '12:00:00')
GROUP BY tags.id
ORDER BY count(*) DESC
I tested with 3 posts with 'SQL' as tag created within 12h and 2 posts with 'PHP' as tag but only one of them created within 12h. The query above then returns:
tag count
SQL 3
PHP 1
To get the most popular tags in descending order from the last 12 hours you could do this:
SELECT tag
FROM tags
WHERE id IN (
SELECT tag_id FROM posts WHERE date >= SUBTIME(NOW(), '12:00:00')
)
ORDER BY COUNT(id) DESC
精彩评论