我有一个springmvc博客，它提供了帖子和评论投票的功能。我想返回前3名用户的基础上，他们已经收到的票数对他们的所有职位和评论。
table：
用户u[id，用户名]
职位p[id，u.id]
注解c[id，p.id，u.id]
后置投票pv[p.id，u.id，类型（1或-1）]
注解\投票cv[c.id，u.id，类型（1或-1）]
下面的语句通过查询两个单独的投票表，然后将总数相加，为我提供了每个用户的总投票数：

SELECT
  (SELECT SUM(type) 
  FROM posts_votes pv 
  JOIN posts p ON p.id = pv.post_id 
  JOIN users u ON u.id = p.user_id  
  WHERE u.id LIKE ?1)
+
  (SELECT SUM(type) 
  FROM comments_votes cv  
  JOIN comments c ON c.id = cv.comment_id 
  JOIN users u ON u.id = c.user_id 
  WHERE u.id LIKE ?1)

对于每个用户id的where子句来说，这很好。。。但现在我只想找到前三名投票最多的用户，我有太多的困难。到目前为止，我的情况是：

SELECT u.id, u.username, IFNULL(SUM(pv.type), 0) AS totalPostVotes
 FROM posts_votes pv
 JOIN posts p ON p.id = pv.post_id
 JOIN users u ON u.id = p.user_id
 GROUP BY u.id ORDER BY totalPostVotes DESC LIMIT 3

上面的语句本身就是这样的：u.id、u.username和totalpostvote按降序排列。下面的评论也是如此：

SELECT u.id, u.username, IFNULL(SUM(cv.type), 0) AS totalCommentVotes
 FROM comment_votes cv
 JOIN comments c ON c.id = cv.comment_id
 JOIN users u ON u.id = c.user_id
 GROUP BY u.id ORDER BY totalCommentVotes DESC LIMIT 3

太好了！但我希望第三列的sum结果本质上是“totalvotes”，并且包含这两个子查询的总和。然后我将按u.id按totalvotes desc limit 3排序。
像这样：

SELECT u.id, u.username, SUM(
                         (SELECT IFNULL(SUM(pv.type), 0) AS totalPostVotes
                          FROM posts_votes pv
                            JOIN posts p ON p.id = pv.post_id
                            JOIN users u ON u.id = p.user_id
                          GROUP BY u.id ORDER BY totalPostVotes DESC LIMIT 1)
                         +
                         (SELECT IFNULL(SUM(cv.type), 0) AS totalCommentVotes
                          FROM comments_votes cv                       
                            JOIN comments c ON c.id = cv.comment_id
                            JOIN users u ON u.id = c.user_id
                          GROUP BY u.id ORDER BY totalCommentVotes DESC LIMIT 1))
   AS totalVotes from users u
   GROUP BY u.id, u.username ORDER BY totalVotes DESC LIMIT 3

id | username | totalVotes
2   user2       11
1   user1       11
29  user29      11

所发生的是totalvotes的结果确实是正确的投票计数，11，对于“顶级”用户，但是这些用户中没有一个是真正的顶级用户，并且正确的投票以其他用户的名义被重复了3次。我甚至不确定用户当时是如何被分类的，因为他们的顺序我不知道。
当我添加select“u.id，u.username”ifnull（sum（））时，子查询是分开工作的（它们为我提供了正确的用户），但是如果我运行整个块，我会得到错误“operand should contain 1 column（s）”，所以我删除它们并恢复为只选择ifnull（sum（））
我还注意到子查询只允许限制1。那我怎么才能拿到前三名呢？我应该在某个地方联合还是“+”就足够了？这相当令人困惑。有人能帮我吗？感谢您的帮助。提前谢谢！
更新代码，谢谢彼得：

SELECT
     u.username,
     pv_sum.total AS postTotal,
     cv_sum.total AS commentTotal,
     IFNULL(pv_sum.total, 0) + IFNULL(cv_sum.total, 0) as totalVotes
     FROM users u
     LEFT JOIN (
          SELECT p.user_id, IFNULL(SUM(pv.type), 0) AS total
          FROM posts p
            JOIN posts_votes pv ON pv.post_id = p.id
          GROUP BY p.user_id
        ) pv_sum ON pv_sum.user_id = u.id
     LEFT JOIN (
          SELECT c.user_id, IFNULL(SUM(cv.type), 0) AS total
          FROM comments c
            JOIN comments_votes cv ON cv.comment_id = c.id
          GROUP BY c.user_id
        ) cv_sum ON cv_sum.user_id = u.id
     GROUP BY u.username, postTotal, commentTotal
     ORDER BY totalVotes DESC LIMIT 3;