mysql - 如何使用mysql计算所有帖子的投票总数

Question

• 发布表 2 列：postid、问题

• 答案表 4 列：ansid (PK)、postid、userid、answer

• 投票表 4 列：postid、userid、ansid、upvote

单个帖子的总赞成票

SELECT sum(votes.upvote) as 'totalupvote' FROM `answers` 
            JOIN post on post.postid=answers.postid
            JOIN votes on votes.ansid=answers.ansid
            WHERE post.postid='1'

如何计算所有帖子的赞成票总数？

标签： mysqlsql

Answer 1

如果您想要每个总数postid：

SELECT postid, sum(upvote) as sumupvote 
FROM votes 
GROUP BY postid

如果您还想要该question列：

select p.postid, p.question, v.sumupvote
from post p inner join (
  SELECT postid, sum(upvote) as sumupvote 
  FROM votes 
  GROUP BY postid
) v on v.postid = p.postid