MYSQL、Max、Group by和Max

Question

我有下面两张桌子。

1.电影详细信息(电影ID，Movie_Name，评分，投票，年份)

2.电影类型(Movie-ID，类型)

我使用下面的查询来执行join，并获得每个类型中评分最高的电影。

select Movie_Name, 
    max(Rating) as Rating,
    Genre from movie_test 
inner join movie_genre 
where movie_test.Movie_ID = movie_genre.Movie_ID 
group by Genre

在输出等级和类型是正确的，但Movie_Name是不正确的。

谁能建议我应该做什么改变，以获得正确的电影名称以及评级和流派。

Answer 1

SELECT  g.*, d.*
FROM    MovieGenre g
        INNER JOIN MovieDetail d
            ON g.MovieID = d.MovieID
        INNER JOIN
        (
            SELECT  a.Genre, MAX(b.Rating) maxRating
            FROM    MovieGenre a
                    INNER JOIN MovieDetail b
                        ON a.MovieID = b.MovieID
            GROUP   BY a.Genre
        ) sub ON    g.Genre = sub.Genre AND
                    d.rating = sub.maxRating

您的架构设计有问题。如果一个Movie可以有多个Genre，而且Genre可以包含在许多Movie上，那么它应该是一个三表设计。

MovieDetails表

(PK)

• MovieName

• MovieRating

的

• MovieID

类型表

(PK)

• GenreName

的

• GenreID

Movie_Genre表

主键MovieID (FK) --与(FK)复合主键

Answer 2

这是一个常见的MySQL问题-在聚合查询中指定非聚合/非聚合-by列。其他版本的SQL不允许您这样做，并会警告您。

当您执行类似的查询时，您选择的是聚合组中的非聚合列。由于许多行具有相同的类型，因此当您选择Movie_Name时，它会从每个组中随机选择一行并显示该行，因为没有通用的算法来猜测您想要的行并返回该行的值。

你可能会问‘为什么它是随机选择的?它可以选择max(评级)所属的那个？’但是其他的聚合列呢，比如avg(Rating)？它在那里选择了哪一行？不管怎样，如果两行有相同的最大值怎么办？因此，它不能有挑选行的算法。

要解决这样的问题，您必须重构查询，如下所示：

select Movie_Name, 
    Rating,
    Genre from movie_test mt
inner join movie_genre 
where movie_test.Movie_ID = movie_genre.Movie_ID 
and Rating = (select max(Rating) from movie_test mt2 where mt.Genre = mt2.Genre
group by Genre
limit 1

这将使用子查询选择评级与该流派的最大评级相同的行。

Answer 3

查询：

SELECT t.Movie_Name,
       t.Rating,
       g.Genre
FROM movie_test t
INNER JOIN movie_genre g ON t.Movie_ID = g.Movie_ID
WHERE t.Movie_ID = (SELECT t1.Movie_ID 
                    FROM movie_test t1
                    INNER JOIN movie_genre g1 ON t1.Movie_ID = g1.Movie_ID
                    WHERE g1.Genre = g.Genre
                    ORDER BY t1.Rating DESC
                    LIMIT 1)