sql:从每个用户中选择投票最多的项目 [英] sql: select most voted items from each user

问题描述

我有一个表vote_benefits"，其架构如下:id (int 11)id_benefit (int 11)评级 (int 11)手机(varchar 10)输入日期(日期)

I have a table "vote_benefits" with the following schema: id (int 11) id_benefit (int 11) rating (int 11) cellphone (varchar 10) inputdate (date)

人们可以为每个项目投票(通过星级评分系统)，给予 1 到 5 颗星.我必须为每个福利获得每个评级的投票数.

People can vote (through a stars rating system) for each item, giving it from 1 to 5 stars. I have to get the number of votes for each rating for each benefit.

内容有点像

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 分组的投票最多的福利中获得.我到了

I want to get from these the most voted benefits grouped by id_benefit. I got as far as

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 得票最多的行:

But I'd like to group these by id_b, getting just the row with the most votes for each id_b:

id_b votes rating
2      6     5
3      4     4

我读过类似SQL - 在一个字段中从另一个字段中选择具有最高记录的不同记录 ，但我不明白如何根据我的需要调整它.

I've read links like SQL - select distinct records in one field with highest records from another field , but I don't understand how to adapt it for my needs.

推荐答案

我相信以下应该可行:

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

这篇关于sql:从每个用户中选择投票最多的项目的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！