mysql - 如何根据用户在特定时期内的声誉来选择用户？

Question

这是我的表结构:

-- users
+----+--------+--------------------+
| id |  name  | other columns ... 
+----+--------+--------------------+

-- votes
+----+---------+---------+-------+------------+
| id | user_id | post_id | value | date_time  |
+----+---------+---------+-------+------------+
| 1  | 234234  | 3443    | 1     | 1442684886 | -- For example
+----+---------+---------+-------+------------+

我想选择上周排名前 20 的用户。我怎样才能做到这一点？

<小时/>

这是我当前的查询:

SELECT SUM(v.value) score , u.*
FROM users u 
INNER JOIN votes
ON u.id = v.user_id
WHERE v.date_time >  UNIX_TIMESTAMP(now() - INTERVAL 1 week)
ORDER BY score DESC
LIMIT 20;

但是我的查询只返回一行。

Answer 1

Select sum(value) 
FROM users  JOIN votes 
ON u.id = v.user_id 
WHERE v.date_time >  UNIX_TIMESTAMP(now() - INTERVAL 1 week) 
GROUP BY name 
ORDER BY sum(value) DESC
LIMIT 20;