力扣---高频SQL题---39---1341. 电影评分

一、题目

二、代码

#考虑字典序  limit1确实可行  等于在limit之前完成筛选



select name as results  from
(
    select MovieRating.user_id, count( MovieRating.user_id ) as co ,Users.name from MovieRating 
    join Users
    on MovieRating.user_id = Users.user_id
    group by MovieRating.user_id
    order by co desc,name asc
    limit 1
) as table1
union all
select table1.title as results  from
(
    select MovieRating.movie_id,avg( MovieRating.rating )  as ave  ,Movies.title from MovieRating 
    join Movies
    on MovieRating.movie_id  = Movies.movie_id      
    where MovieRating.created_at  >= "2020-02-01" and  MovieRating.created_at  <= "2020-02-29"
    group by MovieRating.movie_id    
    order by ave desc, title asc 
    limit 1
) as table1

三、运行结果