MySQL基础练习题16-电影评分

题目

准备数据

分析数据

总结

---

题目

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

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

准备数据

## 创建库
create database db;
use sb;## 创建movies表
Create table If Not Exists Movies (movie_id int, title varchar(30));## 创建users表
Create table If Not Exists Users (user_id int, name varchar(30));## 创建MovieRating表
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');

 输入表：

movies表

 users表

movierating表

分析数据

结果分两个结果，且不相关，因此最后使用union  all进行连接。

1.Daniel 和 Monica 都点评了 3 部电影（"Avengers", "Frozen 2" 和 "Joker"） 但是 Daniel 字典序比较小。2.Frozen 2 和 Joker 在 2 月的评分都是 3.5，但是 Frozen 2 的字典序比较小。

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

第一步：查找评论电影数量最多的用户名。如果出现平局，返回字典序较小的用户名。

with t1 as (select user_id,count(rating) rating from movierating group by user_id
) select min(a.name) resultsfrom users ajoin t1 b on b.user_id = a.user_id
where b.rating = (select max(rating) rating from t1);

第二步：查找在 February 2020 平均评分最高 的电影名称。如果出现平局，返回字典序较小的电影名称。

with t2 as (select movie_id,avg(rating) ratingfrom movieratingwhere created_at between '2020-02-01' and '2020-02-29'group by movie_id
)select min(a.title) resultsfrom movies a join t2 b on b.movie_id = a.movie_id
where b.rating = (select max(rating) rating from t2);

第三步：将两个结果进行连接。

with t1 as (select user_id,count(rating) rating from movierating group by user_id
) ,t2 as (select movie_id,avg(rating) ratingfrom movieratingwhere created_at between '2020-02-01' and '2020-02-29'group by movie_id
)
select min(a.name) resultsfrom users ajoin t1 b on b.user_id = a.user_id
where b.rating = (select max(rating) rating from t1)
union all
select min(a.title) resultsfrom movies a join t2 b on b.movie_id = a.movie_id
where b.rating = (select max(rating) rating from t2);

总结

1. UNION ALL是SQL中的一个操作符，‌用于将两个或多个SELECT语句的结果集合并成一个结果集。‌（所以要对结果进行个整理）
2. UNION ALL的主要功能是将多个查询结果集合并成一个。‌与UNION操作符不同，‌UNION ALL会保留所有的行，‌包括重复的行。‌
3. 使用UNION ALL时，‌每个SELECT语句中选取的列数必须相同，‌并且相应列的数据类型也必须兼容。‌
4. 如果不需要去除重复的行，‌并且希望保留所有检索到的数据，‌包括重复的行，‌那么应该使用UNION ALL。‌
5. UNION ALL返回的结果集不会进行排序，‌如果需要排序，‌应该在查询完成后使用ORDER BY子句进行手动排序。‌