我有一个论坛,我想找到最受欢迎的用户。最受欢迎的用户由帖子和评论中最多的点赞数定义,也由帖子和评论总数定义。拥有最多点赞(顺序1)、最多发帖(顺序2)和最多评论(顺序3)的用户是最受欢迎的。同样的逻辑也适用于下一个(第二)最受欢迎的用户。
所以我有3个表:
posts table
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
users table
id name
1 John
2 Adam
3 Maggie
4 Steve
likes列包含其他用户在相应的帖子(行)上给出的点赞。我试着:
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
显然,这不起作用,因为如果您测试查询,它会给出比预期更多的like (sum)。
下面是实时查询http://sqlfiddle.com/!2/08900/3
查询的问题是用户有多个帖子和多个评论,导致笛卡尔积并产生错误的总和。
下面的查询(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 |
你可以这样做
SELECT
u.id,
u.name,
(l.likes + r.likes) As TotalLikes,
IFNULL(posts,0) AS TotalPosts,
IFNULL(comments,0) AS TotalComments
FROM users AS u
LEFT JOIN (SELECT
user_id,
IFNULL(SUM(likes),0) as likes,
COUNT(likes) as posts
FROM posts
GROUP BY user_id) AS l
on l.user_id = u.id
LEFT JOIN (SELECT
user_id,
IFNULL(SUM(likes),0) as likes,
COUNT(likes) AS comments
FROM comments
GROUP BY user_id) AS r
on r.user_id = u.id
ORDER BY TotalLikes DESc
SQL Fiddle Demo
输出| ID | NAME | TOTALLIKES | TOTALPOSTS | TOTALCOMMENTS |
---------------------------------------------------------
| 4 | Steve | 11 | 2 | 2 |
| 2 | Adam | 5 | 4 | 3 |
| 1 | John | 3 | 7 | 4 |
| 3 | Maggie | 0 | 2 | 0 |