MySQL:选择多个枚举值的计数 [英] MySQL : Selecting count of mutiple enum value

问题描述

我正在制作电影分级应用程序，用户可以在其中将电影分级为喜欢"或不喜欢".所以我做了3个表的用户，电影和评分.投票表的示例是:

I am making a movie rating application where a user can rate a movie as either 'like' or 'dislike'. So I have made 3 tables user, movie and rating. The vote table's example is :

userID    movieID     Vote

 x        a           li
 y        a           dli
 y        b           li
 w        a           li

表的架构为:

userID - PrimaryKey
movieID - PrimaryKey
Vote - Enum('li','dli')

我已经将userID和movieID用作主键，这样，如果用户更新了他/她的喜好，那么如果有记录，则特定行将被更新.

I have made userID and movieID as primary key so that if the user updates his/her preference that specific row gets updated if the record is there.

这是电影表的模式.

mID - PrimaryKey
mName - Varchar
mGenre - Varchar
mDesc -  Text
mDateOfRelease - Date

我的问题是，是否有可能从movie表中选择所有列，并在一个db调用中对该电影的喜欢和不喜欢计数.如果是，该怎么办?

推荐答案

假设您有电影，并且其中包含字段 id .您可以执行以下操作:

Let' imagine that you have movie and it contains field id. Here is what you can do:

select m.*,sum(case when r.vote like 'li' then 1 else 0 end) likes,
sum(case when r.vote like 'dli' then 1 else 0 end) dislikes from movie m
left join rating r
on r.movieID = m.mID
group by m.mID;

左联接基本上是无投票权的.如果该电影的评分表为空-仍将显示它.

Left join is basically for no-votes. If rating table will be empty for this movie - it will still show it.

修改:为了解释这一点，我将减少此查询.

Edit: To explain this I will cut this query.

我们需要了解分组依据在做什么.您可以在此处找到文档.简而言之，我们根据您的group by语句中列的不同条目来创建数据组.

We need to understand what group by is doing. You can find docs here. In short we create groups of the data based on the different entries of the column from your group by statement.

select count(r.movieID) from rating r
group by r.movieID;

这将给我们每部电影获得多少票(这里我们只列出获得任何投票的电影-评分表中的行).因此，在此之后，我们可以进行智能"计数，并使用条件

This will give us how many votes each movie got (here we list only movies that got any vote - line in rating table). So after this we can do "smart" count, and use conditional SUM function. Into the definition you can have an expression. That means case when ... then ... end works as well. So we just sum up all the 'li' and do not touch 'dli' for likes and opposite for dislikes.

唯一的一大缺点是我们没有所有电影(想想电影没有票的情况).然后，此选择将​​仅跳过此步骤.在这里，我们有 LEFT JOIN语句.这个想法很简单-无论评级表是怎么回事，我们都将所有包括在表格电影的所有行中.在上，我们用于将它们连接到表格.然后，我们回想起我们总结但之前所做的工作，将group by更改为mID(它始终存在且不为null，因此我们总有一些要分组的东西).在这种情况下，您将完全拥有所需的内容.还要处理此查询(结果是一个).尝试了解如果您将等级"列留在按组"语句中会发生什么情况.(比阅读大量文本更容易看清:))

The only one big drawback of this is we don't have all the movies (think about the case when there is no votes for a movie). Then this select will just skip this. And here we have LEFT JOIN statement. The idea is very simple - we include all the lines of table movie no matter what is going on in rating table. on we use to connect these to tables. Then we recall what we did before with summing up but change group by to mID (it always exists and non-null, so we always have something to group on). In this case you will have exactly what you want. Also play around with this query (resulting one). Try to understand what will happen if you leave your rating column in group by statement. (It's easier to see than read tons of text :) )

如果有不清楚的地方-请让我知道，将尝试对其进行改进.

If something is not clear - please let me know, will try to improve it.

希望有帮助

这篇关于MySQL:选择多个枚举值的计数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！