mysql - 通过算术运算检索TOP 10数据

Question

我想检索获得投票最多的前 10 个帖子的用户的姓名。计算票数的公式应该是upvotes-downvotes。 我还需要计算该帖子的投票数。

这是我的表 user_info

id       lname        fname
1        abc1         abc1
2        abc2         abc2
3        abc3         abc3
4        abc4         abc4
5        abc5         abc5
6        abc6         abc6
7        abc7         abc7

表格帖子

post_id         post_message        user_id
1               sup                 1
2               good morning        2
3               hello               5
4               test                3
5               sample              4

表决

vote_id      user_id       post_id              vote_type
1            1             1                    upvote
2            2             1                    upvote 
3            1             2                    downvote
4            1             2                    upvote
5            2             2                    downvote
6            3             1                    upvote
7            3             4                    upvote
8            4             4                    downvote

这是我尝试过的查询。

SELECT post_id, fname,lname,COUNT(CASE WHEN vote_type = 'upvote' then 1 ELSE NULL END) as "upvotes", COUNT(CASE WHEN vote_type = 'downvote' then 1 ELSE NULL END) as "downvotes"
FROM user_info 

LEFT JOIN post ON 
post.user_id = user_info.id

LEFT JOIN vote ON
post.user_id = user_info.id 

ORDER BY 'upvotes'- 'downvotes' // is this possible?
LIMIT 10

我想要的输出是这样的。

post_id      lname        fname     vote
1            abc1         abc1      3       // 3 upvotes
4            abc3         abc3      0       // 1upvote - 1 downvote
3            abc5         abc5      0       // no vote
5            abc4         abc4      0       // no vote
2            abc2         abc2     -1       // 1upvote - 2 downvotes

我不知道应该把“投票”列放在哪里，但它应该在我的查询中。

我创建了一个 sql fiddle为此

Answer 1

Fiddle

select p.post_id,u.fname,u.lname,
case when sum(upvotes)+sum(downvotes) is null then 0
else sum(upvotes)+sum(downvotes) end as vote
 from post p left join
 (
 SELECT post_id,
 CASE WHEN vote_type = 'upvote' then 1 ELSE 0 END as "upvotes", 
 CASE WHEN vote_type = 'downvote' then -1 ELSE 0 END as "downvotes"
 FROM vote 
 ) t
 on p.post_id = t.post_id
 left join user_info u on p.user_id = u.id
 group by p.post_id
 order by vote desc, sum(upvotes) desc, sum(downvotes) desc
 limit 10;