How to select top votes in MySQL?
I have table posts
and posts_votes
. In posts_votes
I have id
and post_id
and vote
. Now I want select from posts where have top votes count in last 1 day u开发者_开发知识库sing this to set the time
from_unixtime(post_date) >= SUBDATE(NOW(),1)
maybe something like this:
SELECT
posts.*,
pv.votes
FROM
posts JOIN
(
SELECT post_id, COUNT(*) AS votes FROM posts_votes GROUP BY post_id
) AS pv ON posts.id=pv.post_id
WHERE
posts.post_date >= UNIX_TIMESTAMP()-86400
ORDER BY
pv.votes DESC;
Utilizing the GROUP BY clause allows you to aggregate data on a particular column without resorting to a sub query or derived table.
This query returns all posts, ordered by vote count in descending order:
SELECT p.*
FROM posts p
JOIN posts_votes pv
ON pv.post_id = p.id
WHERE FROM_UNIXTIME(p.post_date) >= SUBDATE(NOW(), 1)
GROUP BY p.id
ORDER BY COUNT(pv.post_id) DESC
If you wanted to limit it to the top ten posts, you could add the LIMIT clause:
SELECT p.*
FROM posts p
JOIN posts_votes pv
ON pv.post_id = p.id
WHERE FROM_UNIXTIME(p.post_date) >= SUBDATE(NOW(), 1)
GROUP BY p.id
ORDER BY COUNT(pv.post_id) DESC
LIMIT 10
MySQL may be able to optimize the date comparison, however, in general, try to perform all of your functions on the constant (the right side) instead of on the column (the left side).
For example, you should change this:
WHERE FROM_UNIXTIME(p.post_date) >= SUBDATE(NOW(), 1)
To this:
WHERE p.post_date >= UNIX_TIMESTAMP(SUBDATE(NOW(), 1))
This way, MySQL doesn't have to run FROM_UNIXTIME() on every row. Again, MySQL may realize this and auto optimize, but it's best practice.
精彩评论