问题描述
我有以下两个表.
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的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!