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

I have two tables:

post:

我有两张桌子：帖子：

id    text
1     abc
2     abcd
3     bcd

and voting:

并投票：

post_id  vote
2         2
1         5
3         1
1         3

I want to show top 10 posts by rating in "votes" table:

我想通过“投票”表中的评分来显示前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"

but it's order by post id. How can i order it by total votes?

但它是按帖子ID排序的。我如何以总票数订购？

2 个解决方案

#1

Probably this is what you need:

可能这就是你需要的：

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

At least I think it would make sense to order by average rating. But of course you can order by total points given:

至少我认为按平均评级排序是有意义的。但当然你可以按总分给出：

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

#2

If you want the top 10 records from post by the votes, then use join and group by:

如果您希望通过投票发布前10条记录，则使用join和group by：

select p.*
from post p join
     voting v
     on p.id = v.post_id
group by p.id
order by count(*) desc
limit 10;

To get the actual votes, include count(*) in the select.

要获得实际投票，请在select中包含count（*）。

#1