力扣高频SQL 50题（基础版）第四十五题之1341.电影评分

力扣高频SQL 50题（基础版）第四十五题

1341.电影评分

题目说明

表：Movies

±--------------±--------+

| Column Name | Type |

±--------------±--------+

| movie_id | int |

| title | varchar |

±--------------±--------+

movie_id 是这个表的主键(具有唯一值的列)。

title 是电影的名字。

表：Users

±--------------±--------+

| Column Name | Type |

±--------------±--------+

| user_id | int |

| name | varchar |

±--------------±--------+

user_id 是表的主键(具有唯一值的列)。

表：MovieRating

±--------------±--------+

| Column Name | Type |

±--------------±--------+

| movie_id | int |

| user_id | int |

| rating | int |

| created_at | date |

±--------------±--------+

(movie_id, user_id) 是这个表的主键(具有唯一值的列的组合)。

这个表包含用户在其评论中对电影的评分 rating 。

created_at 是用户的点评日期。

请你编写一个解决方案：

• 查找评论电影数量最多的用户名。如果出现平局，返回字典序较小的用户名。
• 查找在 February 2020 平均评分最高 的电影名称。如果出现平局，返回字典序较小的电影名称。

字典序 ，即按字母在字典中出现顺序对字符串排序，字典序较小则意味着排序靠前。

实现过程

准备数据

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 MovieRating (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 MovieRating
insert into MovieRating (movie_id, user_id, rating, created_at) values ('1', '1', '3', '2020-01-12')
insert into MovieRating (movie_id, user_id, rating, created_at) values ('1', '2', '4', '2020-02-11')
insert into MovieRating (movie_id, user_id, rating, created_at) values ('1', '3', '2', '2020-02-12')
insert into MovieRating (movie_id, user_id, rating, created_at) values ('1', '4', '1', '2020-01-01')
insert into MovieRating (movie_id, user_id, rating, created_at) values ('2', '1', '5', '2020-02-17')
insert into MovieRating (movie_id, user_id, rating, created_at) values ('2', '2', '2', '2020-02-01')
insert into MovieRating (movie_id, user_id, rating, created_at) values ('2', '3', '2', '2020-03-01')
insert into MovieRating (movie_id, user_id, rating, created_at) values ('3', '1', '3', '2020-02-22')
insert into MovieRating (movie_id, user_id, rating, created_at) values ('3', '2', '4', '2020-02-25')

实现方式

(select u.name as results
from MovieRating as m
left join users as u 
on m.user_id = u.user_id
group by m.user_id
order by count(*) desc, u.name
limit 1)
union all
(select mo.title
from MovieRating as m
left join movies as mo
on m.movie_id = mo.movie_id
where m.created_at like '2020-02%'
group by mo.movie_id
order by avg(m.rating) desc, mo.title
limit 1);

结果截图