mysql - 从一个表中选择行，其中值和顺序来自 MYSQL 中的另一个表

Question

我有两个表:

帖子:

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"

但它是按帖子编号排序的。如何按总票数排序？

Answer 1

可能这就是你需要的:

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