mysql - 根据喜欢、帖子和评论选择最受欢迎的用户

Question

我有一个论坛，我想找到最受欢迎的用户。最受欢迎的用户由帖子和评论上最多的点赞数定义，也由最多的帖子和评论定义。拥有最多赞(顺序 1)、最多帖子(顺序 2)和最多评论(顺序 3)的用户是最受欢迎的。同样的逻辑适用于下一个(第二)最受欢迎的用户。

所以我有 3 个表:

帖子表

id      user_id     likes
1       1           0
2       1           0
3       1           0
4       1           0
5       1           0
6       1           1
7       1           0
8       2           0
9       2           2
10      2           0
11      2           0
12      3           0
13      3           0
14      4           0
15      4           10

评论表

id      user_id     likes
1       1           0
2       1           1
3       1           1
4       1           0
5       2           0
6       2           2
7       2           1
8       4           1
9       4           0

用户表

id      name
1       John
2       Adam
3       Maggie
4       Steve

点赞列包含其他用户在相应帖子(行)上给出的点赞。我试过:

 SELECT DISTINCT c.id, c.name,
    SUM(a.likes), SUM(b.likes), (SUM(a.likes) + SUM(b.likes)) as popular,
    COUNT(a.id) as mostp, COUNT(b.id) as mostc
    FROM posts as a, comments as b, users as c
    WHERE a.user_id=b.user_id AND a.user_id=c.id AND b.user_id=c.id
    GROUP BY a.user_id, b.user_id ORDER BY popular DESC, mostp DESC, mostc DESC

显然，这是行不通的，因为如果您测试查询，它会给出比预期更多的点赞数(总和)。

这是实时查询 http://sqlfiddle.com/#!2/08900/3

Answer 1

您的查询的问题在于拥有多个帖子和多个评论的用户，导致笛卡尔积并产生错误的总和。

下面的查询(SQL Fiddle 上的示例)应该有效，因为子查询已经按 user_id 分组:

SELECT
  u.name,
  COALESCE(p.likes,0) + COALESCE(c.likes,0) AS likes,
  COALESCE(p.cnt,0) AS post_count,
  COALESCE(c.cnt,0) AS comment_count
FROM users u
LEFT JOIN (
  SELECT user_id, COUNT(1) AS cnt, SUM(likes) AS likes
  FROM posts
  GROUP BY user_id
) p ON ( p.user_id = u.id )
LEFT JOIN (
  SELECT user_id, COUNT(1) AS cnt, SUM(likes) AS likes
  FROM comments
  GROUP BY user_id
) c ON ( c.user_id = u.id )
ORDER BY likes DESC, post_count DESC, comment_count DESC;

结果:

|   NAME | LIKES | POST_COUNT | COMMENT_COUNT |
-----------------------------------------------
|  Steve |    11 |          2 |             2 |
|   Adam |     5 |          4 |             3 |
|   John |     3 |          7 |             4 |
| Maggie |     0 |          2 |             0 |