MySQL选择并按值分组 [英] mySQL select and group by values

问题描述

我想按特定值对行进行计数和分组.这似乎很简单，但是我似乎做不到.

I'd like to count and group rows by specific values. This seems fairly simple, but I can't seem to do it.

我有一个与此类似的表格:

I have a table set up similar to this:

Table: Ratings
id pID uID rating
1  1   2     7
2  1   7     7
3  1   5     4
4  1   1     1

id是主键，piD和uID是外键.评分包含1到10之间的值，并且只能包含1到10之间的值.

id is the primary key, piD and uID are foreign-keys. Rating contains values between 1 and 10, and only between 1 and 10.

我想运行一些统计数据并计算具有一定值的评级数.在上面的示例中，两个人的评分为7.

I want to run some statistics and count the number of ratings with a certain value. In the example above, two have left a rating of 7.

所以我写了以下查询:

SELECT COUNT(*) AS 'count' , 'rating'
FROM 'ratings'
WHERE pID= '1'
GROUP BY `rating`
ORDER BY `rating`

哪个会产生很好的结果:

Which yields the nice result as:

count  ratings
1      1
1      4
2      7

我想让mySQL查询也包含1到10之间的值.

I'd like to get the mySQL query to include values between 1 and 10 as well.

例如:

Desired Result
count  ratings
1      1
0      2
0      3
1      4
0      5
0      6
2      7
0      8
0      9
0      10

不幸的是，我对SQL还是比较陌生，在过去的一个小时里，我一直在阅读所有可以使用的内容，但我无法使其正常工作.我一直遵循某种JOIN的思路.

Unfortunately, I'm relatively new to SQL and I've been reading through everything I could get my hands on for the past hour, but I can't get it to work. I've been leaning along the lines of a some type of JOIN.

如果有人能指出我正确的方向，那将不胜感激.

If anyone can point me in the right direction, it'd be appreciated.

谢谢.

推荐答案

我想不出任何函数或方法来直接获得一系列所需的等级.我唯一能想到的就是拥有一个表，其中包含允许的评分范围.

I can't think of any function, or methodology, to directly get a range of ratings like what you need. The only thing I can think of is to have a table containing the allowable range of ratings.

Table: AllowedRatings
rating
1
2
3
4
5
6
7
8
9
10

然后使用它对您的评分表进行子查询:

Then use this to do a subquery with your ratings table:

SELECT (SELECT COUNT(*) 
          FROM ratings 
         WHERE ratings.rating = AllowedRatings.rating 
           AND pid = '1'
       )
     , rating
FROM AllowedRatings
ORDER BY rating

我知道这也可以通过联接来完成，但是那会更复杂，而且老实说，我有点太累了，无法解决它，或者正确地设置了一个数据库来对其进行测试，但是子查询方法应该工作.

I know this could also be done by a join, but that would be more complicated, and I'm honestly a bit too tired to work it out, or set up a DB correctly to test it, but the subquery method should work.

此方法的优点是您可以为评级添加更多信息，例如说明(例如:1 = 'Sucks', 3 = 'Meh', 5 = 'Ok', 7 = 'Worthwhile', 10 = 'Amazing!')或更改范围，而无需编辑硬编码值

The advantage of this method is that you could put more information in for your ratings, like a description (eg: 1 = 'Sucks', 3 = 'Meh', 5 = 'Ok', 7 = 'Worthwhile', 10 = 'Amazing!') or change your range without having to edit hard-coded values

这篇关于MySQL选择并按值分组的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！