基于其他表的SQL查询 [英] SQL Query based on other table

问题描述

我有一个标准化的表:

`Table: TheMovies`
id      | MovieName 
---------------------
1       | Zootopia 
2       | Moana 
3       | Toy Story

`Table: TheGenres`
id      | GenreName 
---------------------
21      | Action 
22      | Animation 
23      | Adventure

`Table: mMoviesGenres`
movieID | genreID 
---------------------
1       | 21 
1       | 23 
2       | 22
2       | 21 
3       | 23
3       | 21

一切正常，但我需要一个查询来向我显示相同类型的相似电影(在我们的情况下，我们需要MovieID = 1的相似电影，其结果应输出MovieID = 3).

All works fine, but I need a query which will show me similar movies in same genres (in our case we need similar movies for MovieID = 1 which should output MovieID = 3 as result).

您能给我一个SQL查询，以便我有一个基本的想法，以便能够创建更高级的查询吗?

Can you give me an SQL query so I have a basic idea of doing that, to be able to create more advanced queries?

到目前为止，我的查询是:

My query, so far, is:

SELECT 
    TheMovies.* 
FROM 
    mMoviesGenres 
        JOIN TheMovies ON mMoviesGenres.movieID = TheMovies.id 
WHERE 
    mMoviesGenres.genreID IN 
        (
            SELECT 
                genreID 
            FROM 
                mMoviesGenres 
            WHERE 
                movieID = 1
        )

**在我看来，表格:TheMovies不需要执行我所要求的

** In my Opinion, the Table: TheMovies is not needed to do what I ask for

推荐答案

尝试以下查询:

SELECT m2.movieId
FROM mMoviesGenres m1
INNER JOIN mMoviesGenres m2
    ON m1.genreID = m2.genreID
WHERE m1.movieId = 1 AND
      m2.movieId <> 1
GROUP BY m2.movieId
HAVING COUNT(*) = (SELECT COUNT(*) FROM mMoviesGenres WHERE movieId = 1)

更新:

如果要查找至少两种类型相似的电影，请使用此HAVING子句:

If you want to find movies which are similar with regard to at least two genres, then use this HAVING clause:

HAVING COUNT(*) >= 2

这篇关于基于其他表的SQL查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！