1341 电影评分 注意union 的括号问题

Create table If Not Exists Movies (movie_id int, title varchar(30))
Create table If Not Exists Users (user_id int, name varchar(30))
Create table If Not Exists Movie_Rating (movie_id int, user_id int, rating int, created_at date)
Truncate table Movies
insert into Movies (movie_id, title) values ('1', 'Avengers')
insert into Movies (movie_id, title) values ('2', 'Frozen 2')
insert into Movies (movie_id, title) values ('3', 'Joker')
Truncate table Users
insert into Users (user_id, name) values ('1', 'Daniel')
insert into Users (user_id, name) values ('2', 'Monica')
insert into Users (user_id, name) values ('3', 'Maria')
insert into Users (user_id, name) values ('4', 'James')
Truncate table Movie_Rating
insert into Movie_Rating (movie_id, user_id, rating, created_at) values ('1', '1', '3', '2020-01-12')
insert into Movie_Rating (movie_id, user_id, rating, created_at) values ('1', '2', '4', '2020-02-11')
insert into Movie_Rating (movie_id, user_id, rating, created_at) values ('1', '3', '2', '2020-02-12')
insert into Movie_Rating (movie_id, user_id, rating, created_at) values ('1', '4', '1', '2020-01-01')
insert into Movie_Rating (movie_id, user_id, rating, created_at) values ('2', '1', '5', '2020-02-17')
insert into Movie_Rating (movie_id, user_id, rating, created_at) values ('2', '2', '2', '2020-02-01')
insert into Movie_Rating (movie_id, user_id, rating, created_at) values ('2', '3', '2', '2020-03-01')
insert into Movie_Rating (movie_id, user_id, rating, created_at) values ('3', '1', '3', '2020-02-22')
insert into Movie_Rating (movie_id, user_id, rating, created_at) values ('3', '2', '4', '2020-02-25')

select d.results
from
#外围自然有括号
(   
    #注意要有括号
    #评论电影数量最多的用户名。
    (select   c.name as 'results'
    
    from  Movie_Rating a

    left join Movies b  on a.movie_id = b.movie_id
    left join Users  c  on a.user_id  = c.user_id
    group by c.name
    order by count(distinct a.movie_id) desc,c.name
    limit 1  )

union 
    #注意要有括号
    #在 2020 年 2 月 平均评分最高 的电影名称。
    (select b.title as 'results'
    
    from  Movie_Rating a
    left join Movies b  on a.movie_id = b.movie_id
    where  substr( created_at,1,7) = '2020-02'
    group by b.title
    order by avg(rating) desc, b.title
    limit 1)  
 ) d