电影评分
请你编写一组 SQL 查询:查找评论电影数量最多的用户名,如果出现平局,返回字典序较小的用户名。查找在2020 年 2 月 平均评分最高的电影名称,如果出现平局,返回字典序较小的电影名称。
展示效果:
+--------------+
| results |
+--------------+
| Daniel |
| Frozen 2 |
+--------------+
建表语句:
Create table If Not Exists 77_Movies (movie_id int, title varchar(30));
Create table If Not Exists 77_Users (user_id int, name varchar(30));
Create table If Not Exists 77_Movie_Rating (movie_id int, user_id int, rating int, created_at date);
Truncate table 77_Movies;
insert into 77_Movies (movie_id, title) values ('1', 'Avengers');
insert into 77_Movies (movie_id, title) values ('2', 'Frozen 2');
insert into 77_Movies (movie_id, title) values ('3', 'Joker');
Truncate table 77_Users;
insert into 77_Users (user_id, name) values ('1', 'Daniel');
insert into 77_Users (user_id, name) values ('2', 'Monica');
insert into 77_Users (user_id, name) values ('3', 'Maria');
insert into 77_Users (user_id, name) values ('4', 'James');
Truncate table 77_Movie_Rating;
insert into 77_Movie_Rating (movie_id, user_id, rating, created_at) values ('1', '1', '3', '2020-01-12');
insert into 77_Movie_Rating (movie_id, user_id, rating, created_at) values ('1', '2', '4', '2020-02-11');
insert into 77_Movie_Rating (movie_id, user_id, rating, created_at) values ('1', '3', '2', '2020-02-12');
insert into 77_Movie_Rating (movie_id, user_id, rating, created_at) values ('1', '4', '1', '2020-01-01');
insert into 77_Movie_Rating (movie_id, user_id, rating, created_at) values ('2', '1', '5', '2020-02-17');
insert into 77_Movie_Rating (movie_id, user_id, rating, created_at) values ('2', '2', '2', '2020-02-01');
insert into 77_Movie_Rating (movie_id, user_id, rating, created_at) values ('2', '3', '2', '2020-03-01');
insert into 77_Movie_Rating (movie_id, user_id, rating, created_at) values ('3', '1', '3', '2020-02-22');
insert into 77_Movie_Rating (movie_id, user_id, rating, created_at) values ('3', '2', '4', '2020-02-25');
最终SQL:
SELECT
name result
from
(
SELECT
`name`
from
(SELECT
u.`name` `name`,
COUNT(*) num
from
77_Movie_Rating m
LEFT JOIN
77_Users u
on
m.user_id=u.user_id
GROUP BY m.user_id) t
ORDER BY num desc ,name
limit 1) t
union
(
SELECT
mo.title
from
77_Movie_Rating m
LEFT JOIN
77_Movies mo
on
m.movie_id=mo.movie_id
where DATE_FORMAT(created_at,'%Y-%m')='2020-02'
GROUP BY m.movie_id
ORDER BY avg(m.rating) desc,title
limit 1)