Table: Movies
+---------------+---------+
| Column Name | Type |
+---------------+---------+
| movie_id | int |
| title | varchar |
+---------------+---------+
movie_id is the primary key for this table.
title is the name of the movie.
Table: Users
+---------------+---------+
| Column Name | Type |
+---------------+---------+
| user_id | int |
| name | varchar |
+---------------+---------+
user_id is the primary key for this table.
Table: Movie_Rating
+---------------+---------+
| Column Name | Type |
+---------------+---------+
| movie_id | int |
| user_id | int |
| rating | int |
| created_at | date |
+---------------+---------+
(movie_id, user_id) is the primary key for this table.
This table contains the rating of a movie by a user in their review.
created_at is the user's review date.
Write the following SQL query:
- Find the name of the user who has rated the greatest number of movies.
In case of a tie, return lexicographically smaller user name.
- Find the movie name with the highest average rating in February 2020.
In case of a tie, return lexicographically smaller movie name.
The query is returned in 2 rows, the query result format is in the following example:
Movies table:
+-------------+--------------+
| movie_id | title |
+-------------+--------------+
| 1 | Avengers |
| 2 | Frozen 2 |
| 3 | Joker |
+-------------+--------------+
Users table:
+-------------+--------------+
| user_id | name |
+-------------+--------------+
| 1 | Daniel |
| 2 | Monica |
| 3 | Maria |
| 4 | James |
+-------------+--------------+
Movie_Rating table:
+-------------+--------------+--------------+-------------+
| movie_id | user_id | rating | created_at |
+-------------+--------------+--------------+-------------+
| 1 | 1 | 3 | 2020-01-12 |
| 1 | 2 | 4 | 2020-02-11 |
| 1 | 3 | 2 | 2020-02-12 |
| 1 | 4 | 1 | 2020-01-01 |
| 2 | 1 | 5 | 2020-02-17 |
| 2 | 2 | 2 | 2020-02-01 |
| 2 | 3 | 2 | 2020-03-01 |
| 3 | 1 | 3 | 2020-02-22 |
| 3 | 2 | 4 | 2020-02-25 |
+-------------+--------------+--------------+-------------+
Result table:
+--------------+
| results |
+--------------+
| Daniel |
| Frozen 2 |
+--------------+
Daniel and Monica have rated 3 movies ("Avengers", "Frozen 2" and "Joker") but Daniel is smaller lexicographically.
Frozen 2 and Joker have a rating average of 3.5 in February but Frozen 2 is smaller lexicographically.
解题思路:先将daniel 选出来,再将符合条件的frozen 2选出来,然后两者union
select name as results
from
(SELECT m.user_id, name,
count(m.user_id) over (partition by m.user_id) as cnt
from movie_rating m, users u
where m.user_id = u.user_id
order by cnt desc, name
limit 1) t1
union
select title
from
(select m.movie_id, title,
avg(rating) over (partition by m.movie_id) as avg_rate
from movie_rating m, movies mv
where created_at between '2020-02-01' and '2020-02-29'
and m.movie_id = mv.movie_id
order by avg_rate desc, title
limit 1) t2
select *
from (select name as results
from
(SELECT user_id, count(rating) as cnt
from movie_rating
group by user_id) t1, users u
where t1.user_id = u.user_id
order by cnt desc, name
limit 1) t3
union
select * from (select title
from
(select movie_id, avg(rating) as avg_rate
from movie_rating
where created_at between '2020-02-01' and '2020-02-29'
group by movie_id) t2, movies m
where t2.movie_id = m.movie_id
order by avg_rate desc, title
limit 1) t4
相比解法1,解法2多套用了一层select,因为union 前后的select不允许有order by 和limit by,所以要在结果外再套一层select才没有错误。
考察点:UNION注入应用场景
-
只有最后一个SELECT 字句允许有ORDER BY;
select * from users order by id union select 1,2,3;错误
-
只有最后一个SELECT字句允许有LIMIT
select * from users limit 0,1 union select 1,2,3 错误
-
只要UNION连接的几个查询字段数一样且列的数据类型转换没有问题,就可以查询出结果
insert update 数据库信息时也不能使用UNION注入 -
注入页面有回显