如何创建MySQL JOIN查询以仅选择一个表中的行，其中对该行的一定数量的引用存在于另一个表中？

I have two tables in my database, called ratings and movies.

我的数据库中有两个表,称为评级和电影。

Ratings:

| id | movie_id | rating |

| id | movie_id |评级|

Movies:

| id | title |

| id |标题|

A typical movie record might be like this:

典型的电影记录可能是这样的:

| 4 | Cloverfield (2008) |

| 4 | Cloverfield(2008)|

and there may be several rating records for Cloverfield, like this:

并且Cloverfield可能有几个评级记录,如下所示:

| 21 | 4 | 3 | (rating number 21, on movie number 4, giving it a rating of 3)

| 21 | 4 | 3 | (评分号码21,电影号码4,评分为3)

| 22 | 4 | 2 | (rating number 22, on movie number 4, giving it a rating of 2)

| 22 | 4 | 2 | (评分号码22,电影号码4,评分为2)

| 23 | 4 | 5 | (rating number 23k on movie number 4, giving it a rating of 5)

| 23 | 4 | 5 | (电影号码4的评分号码为23k,评分为5)

The question:

How do I create a JOIN query for only selecting the rows in the movie table that have more than x number of ratings in the ratings table? For example, in the above example if Cloverfield only had one rating in the ratings table and x was 2, it would not be selected.

如何创建JOIN查询以仅选择影视表中在评级表中具有超过x个评级数的行?例如,在上面的示例中,如果Cloverfield在评级表中只有一个评级且x为2,则不会选择它。

Thanks for any help or advice!

感谢您的帮助或建议!

5 个解决方案

#1

Use the HAVING clause. Something along these lines:

使用HAVING子句。这些方面的东西:

SELECT movies.id, movies.title, COUNT(ratings.id) AS num_ratings 
  FROM movies 
  LEFT JOIN ratings ON ratings.movie_id=movies.id 
  GROUP BY movies.id 
  HAVING num_ratings > 5;

#2

You'll probably want to use MySQL's HAVING clause

你可能想要使用MySQL的HAVING子句

http://www.severnsolutions.co.uk/twblog/archive/2004/10/03/havingmysql

#3

The JOIN method is somewhat stilted and confusing because that's not exactly what it was intended to do. The most direct (and in my opinion, easily human-parseable) method uses EXISTS:

JOIN方法有点笨拙和令人困惑,因为这并不是它的目的。最直接的(在我看来,易于人类解析的)方法使用EXISTS:

SELECT whatever
  FROM movies m
 WHERE EXISTS( SELECT COUNT(*) 
                 FROM reviews
                WHERE movie_id  = m.id
               HAVING COUNT(*)  > xxxxxxxx )

Read it out loud -- SELECT something FROM movies WHERE there EXIST rows in Reviews where the movie_id matches and there are > xxxxxx rows

大声朗读 - 从电影中选择一些东西在评论中有哪些EXIST行,其中movie_id匹配并且有> xxxxxx行

#4

SELECT * FROM movies 
INNER JOIN
(SELECT movie_id, COUNT(*) as num_ratings from ratings GROUP BY movie_id) as movie_counts
ON movies.id = movie_counts.movie_id
WHERE num_ratings > 3;

That will only get you the movies with more than 3 ratings, to actually get the ratings with it will take another join. The advantage of a subquery over HAVING is you can aggregate the ratings at the same time. Such as (SELECT movie_id, COUNT(*), AVG(rating) as average_move_rating ...)

这只会让你获得超过3个评级的电影,实际上获得它的评级将需要另一个加入。子查询优于HAVING的优点是您可以同时聚合评级。例如(SELECT movie_id,COUNT(*),AVG(rating)as average_move_rating ......)

Edit: Oops, you can aggregate with the having method to. :)

编辑:糟糕,您可以使用having方法进行聚合。 :)

#5

The above solutions are okay for the scenario you mentioned. My suggestion may be overkill for what you have in mind, but may be handy for other situations:

上述解决方案适用于您提到的方案。我的建议可能对你的想法有些过分,但对其他情况可能很方便:

Subquery only those from the ratings table having more than the number you need (again using tha group by having clause):

子查询只有那些来自评级表的数量超过你需要的数量(再次使用tha group by having子句):

select movie_id from ratings group by movie_id having count (*) > x

通过具有count(*)> x的movie_id从评级组中选择movie_id
Join that subquery with the movies table

将该子查询与电影表一起加入

select movies.id from movies join as MoviesWRatings on movies.id = MoviesWRatings.movie_id

从movies.id = MoviesWRatings.movie_id中选择movies.id作为MoviesWRatings加入电影

When you're doing more stuff to the subquery, this might be helpful. (Not sure if the syntax is right for MySQL, please fix if necessary.)

当您在子查询中执行更多操作时,这可能会有所帮助。 (不确定语法是否适合MySQL,如有必要请修复。)

#1