我有两个表：
发布：
id text
1 abc
2 abcd
3 bcd
和投票：
post_id vote
2 2
1 5
3 1
1 3
我想在“投票”表中按评分显示排名前10的帖子：
SELECT * FROM post
WHERE id IN
(SELECT post_id FROM
(SELECT SUM(vote) as totalvote, post_id
FROM voting
GROUP BY post_id) as table1
ORDER BY totalvote DESC)
LIMIT 10"
但这是按帖子ID排序的。如何以总票定购？
可能这是您需要的：
SELECT post.id, post.text FROM post
JOIN voting ON voting.post_id = post.id
GROUP BY post.id
ORDER BY AVG(vote) DESC
LIMIT 10
至少我认为按平均评分排序是有意义的。但是，您当然可以按给出的总积分进行订购：
SELECT post.id, post.text FROM post
JOIN voting ON voting.post_id = post.id
GROUP BY post.id
ORDER BY SUM(vote) DESC
LIMIT 10
本文收集自互联网，转载请注明来源。
如有侵权，请联系[email protected] 删除。
我来说两句