2021-06-17

电影评分

请你编写一组 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) 

上一篇:SQLZOOL练习题答案和解析 第7关 More JOIN operations


下一篇:Pandas-缺失值处理