mysql - sql:选择每个用户投票最多的项目

Question

我有一个表“vote_benefits”，其架构如下:id(整数11)id_好处(int 11)评级(整数 11)手机(varchar 10)输入日期(日期)

人们可以对每个项目进行投票(通过星级评分系统)，给予 1 到 5 颗星。我必须获得每个好处的每个评级的票数。

内容类似于

id id_b rating cellphone  inputdate
1   2   5   123456789   2011-01-04 18:56:38
2   2   4   123456789   2011-01-04 19:03:27
3   2   4   123456789   2011-01-05 10:24:29
4   2   4   123456789   2011-01-05 10:24:33
5   2   5   123456789   2011-01-05 10:24:37
6   2   5   123456789   2011-01-05 10:24:42
7   2   4   123456789   2011-01-05 10:24:43
8   2   5   123456789   2011-01-05 10:24:44
9   2   4   123456789   2011-01-05 10:24:45
10  2   5   123456789   2011-01-05 10:24:48
11  2   5   123456789   2011-01-05 10:25:42
12  3   4   123456789   2011-01-05 10:49:20
13  3   5   123456789   2011-01-05 10:49:21
14  3   4   123456789   2011-01-05 10:49:22
15  3   5   123456789   2011-01-05 10:52:44
16  3   5   123456789   2011-01-05 10:52:45
17  3   4   123456789   2011-01-05 10:53:04
18  3   4   123456789   2011-01-05 10:53:05

我想从这些按 id_benefit 分组的投票最多的福利中获取。我已经做到了

SELECT id_benefit, count( * ) AS votes, rating
FROM `vote_benefits 
GROUP BY id_benefit, rating

这给了我

id_b votes rating
2   5      4
2   6      5
3   4      4
3   3      5

但我想按 id_b 对它们进行分组，只获取每个 id_b 得票最多的行:

id_b votes rating
2      6     5
3      4     4

我读过像 SQL - select distinct records in one field with highest records from another field 这样的链接，但我不明白如何根据我的需要调整它。

Answer 1

我相信以下应该有效:

SELECT id_b, MAX(votes), rating 
FROM (SELECT id_benefit, count( * ) AS votes, rating
        FROM `vote_benefits 
    GROUP BY id_benefit, rating) 
GROUP BY id_b