题目
准备数据
分析数据
总结
题目
-
查找评论电影数量最多的用户名。如果出现平局,返回字典序较小的用户名。
-
查找在
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);
总结
- UNION ALL是SQL中的一个操作符,用于将两个或多个SELECT语句的结果集合并成一个结果集。(所以要对结果进行个整理)
- UNION ALL的主要功能是将多个查询结果集合并成一个。与UNION操作符不同,UNION ALL会保留所有的行,包括重复的行。
- 使用UNION ALL时,每个SELECT语句中选取的列数必须相同,并且相应列的数据类型也必须兼容。
- 如果不需要去除重复的行,并且希望保留所有检索到的数据,包括重复的行,那么应该使用UNION ALL。
- UNION ALL返回的结果集不会进行排序,如果需要排序,应该在查询完成后使用ORDER BY子句进行手动排序。