MySQL:需要返回投票最多的前 3 名用户。两个子查询的 SUM 中的一列中需要的结果。 Java/ Spring MVC

Question

我有一个带有帖子和评论投票功能的 Spring MVC 博客。我想根据他们在所有帖子和评论中收到的投票数返回前 3 名用户。

表格:

users u [id, username]

posts p [id, u.id]

comments c [id, p.id, u.id]

post_votes pv [p.id, u.id, type (1 or -1)]

comment_votes cv [c.id, u.id, type (1 or -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 子句，这可以很好地工作...但是现在我试图找到投票最多的前 3 个用户，但我遇到了太多困难。这是我到目前为止所拥有的:

 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”并包含这两个子查询的总和。然后我将 GROUP BY u.id ORDER BY 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，对于“顶级”用户，但这些用户都不是真正的顶级用户，正确的投票以其他用户。我什至不确定此时用户是如何排序的，因为他们不是我认识的顺序。

当我添加 SELECT "u.id, u.username "IFNULL(SUM()) 时，子查询单独工作(它们给了我正确的用户)但是如果我运行整个 block ，我得到错误“操作数应该包含 1 列”所以我删除它们并恢复为仅 SELECT IFNULL(SUM())

我还注意到子查询只允许 LIMIT 1 个。那么我怎样才能获得前 3 个呢？我应该在某个地方做 UNION 还是“+”就足够了？这相当令人困惑。有人可以帮我吗？任何帮助表示赞赏。提前致谢!

更新代码，谢谢 Peter:

     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;

Answer 1

不要将子查询放在 SELECT 部分，而是将它们加入用户表:

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 post_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 comment_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.id
ORDER BY totalVotes DESC
LIMIT 3;

fiddle :http://sqlfiddle.com/#!9/980cb2/11