MYSQL，Max，Group by和Max [英] MYSQL, Max,Group by and Max

问题描述

我有以下两个表.

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

1.Movie Detail (Movie-ID,Movie_Name,Rating,Votes,Year)

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

2.Movie Genre (Movie-ID,Genre)

我正在使用以下查询执行加入并获取每个电影中评分最高的电影 流派.

I am using the following query to perform join and get the movie with highest rating in each genre.

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

在输出中，等级"和类型"正确，但电影名称"不正确.

In the output Rating and Genre are correct but the Movie_Name is incorrect.

任何人都可以建议我应该进行哪些更改以获取正确的电影名称以及等级和流派.

can anyone suggest what changes I should make to get the correct movie name along with rating and genre.

推荐答案

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中，则它应该是三表设计.

There is something wrong with your schema design. If a Movie can have many Genre as well as Genre can be contain on many Movie, it should be a three table design.

电影详细信息表

• MovieID(PK)
• 电影名称
• 电影评分

流派表

• GenreID(PK)
• GenreName

电影类型表

• MovieID(FK)-具有GenreID的复合主键
• GenreID(FK)

这篇关于MYSQL，Max，Group by和Max的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！