MYSQL，Max，Group by和Max

本文介绍了MYSQL，Max，Group by和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)