基于2列的重复行的SQL查询

Question

我有3个表 Movie ，评分和审阅者

电影有4列 Movief 标题，年，导演

等级有4列 exighteriD Movief在

​

我如何查询评论者谁对同一电影的评分超过1次，并在第二次评论中给出了更高的评分。

这是我在查询2列中找到具有重复值的行的尝试（这意味着电影已由1个审阅者评为不止一次），然后我需要以某种方式查询审阅者谁给出了更高的 >星星第二次评论。

SELECT reviewer.name, movie.title, rating.stars, rating.ratingDate
FROM rating
INNER JOIN reviewer ON reviewer.rID = rating.rID
INNER JOIN movie ON movie.mID = rating.mID
WHERE rating.rID IN (SELECT rating.rID FROM rating GROUP BY rating.rID, rating.mID HAVING COUNT(*) > 1)
ORDER BY reviewer.name, rating.ratingDate;

电影表

电影ID	年级	Victor	导演
101	与风车	1939年	Fleming
102	Star Wars	1977	George Lucas
103	音乐之声	1965	Robert Wise
104	E.T.	1982	Steven Spielberg
105	Titanic	1997	James Cameron
106	Snow White	1937	null
107	Avatar	2009	James Cameron
108	Raiders of the Lost Ark	1981	Steven Spielberg

rating table

reviewerID	movie ID	Stars	ratingDate
201	101	2	2011-01-22
201	101	4	2011-01-27
202	106	4	零
203	103	2	2011-01-20
203	108	4	2011-01-12
203	108	2	2011-01-01-30
204 204	101	3	2011-01-09
205 205	103	3	2011-01-- 27
205	104	2	2011-01-22
205	108	4	零
206	107	3	2011-01-15
206 106	106	5	2011-01-19
207 107	107	5	2011-01-20
208	104 104 104	3	2011-01-02

评论者/code>表

审查	名称
201	Sarah Martinez
202	Daniel Lewis
203	Brittany Harris
204	Mike Anderson
205	Chris Jackson
206	Elizabeth Thomas
207	James Cameron
208	Ashley White

预期结果

审稿人	标题
Sarah Martinez	与风

编辑：我使用MySQL版本8.0.29。

Answer 1

使用：

select re.Name,mo.Title
FROM (
      select reviewerID,movieID,ratingDate,Stars
      from rating r 
      where exists (select 1 
              from rating r1 
              where r1.reviewerID=r.reviewerID 
              and r.movieID=r1.movieID 
              and r.ratingDate>r1.ratingDate
              and r.Stars>r1.Stars
       )) as t1
inner join movie mo on t1.movieID=mo.movieID
inner join reviewer re on t1.reviewerID=re.reviewerID

关键部分是存在语句

where exists (select 1 
              from rating r1 
              where r1.reviewerID=r.reviewerID 
              and r.movieID=r1.movieID 
              and r.ratingDate>r1.ratingDate
              and r.Stars>r1.Stars 

，它将仅返回您拥有相同用户多个电影的结果，等级星星比Previos大于Previos。

Answer 2

we don't need to use where in with rating and join with rating

You can try to use 和 Movief

> LEAD 窗口函数要获取下一个启动每个 ReviewerId 您的逻辑要比较旧的开始更新的开始。

SELECT DISTINCT r.Name,m.Title
FROM (
  SELECT reviewerID, 
         movieID,
         Stars,
         LEAD(Stars) OVER(PARTITION BY reviewerID, movieID ORDER BY ratingDate) n_start
  FROM rating 
) t1
INNER JOIN movie m ON t1.movieID = m.movieID
INNER JOIN reviewer r ON r.reviewerID = t1.reviewerID
WHERE Stars < t1.n_start

此示例数据