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

Question

我有3张 table movie , rating和reviewer

movie有 4 列 movieID , title , year , director

rating有 4 列 reviewerID , movieID , stars , ratingDate

reviewer有 2 列 reviewerID , name

如何查询reviewer对同一部电影评分超过 1 次并在第二次评论时给予更高评分的人。

这是我尝试在查询中查找 2 列中具有重复值的行(这意味着这部电影已被 1 个评论者多次评分)，然后不知何故我需要查询 reviewer谁给予的更高stars第二次审核时。

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;

movie表

<表类=“s-表”><标题>电影ID标题年导演 <正文>101随风而逝1939维克多·弗莱明102星球大战1977乔治·卢卡斯103音乐之声1965罗伯特·怀斯104E.T.1982史蒂文·斯 PIL 伯格105泰坦尼克号1997詹姆斯·卡梅隆106白雪公主1937空107头像2009詹姆斯·卡梅隆108夺宝奇兵1981史蒂文·斯 PIL 伯格

rating表

<表类=“s-表”><标题>评论者ID电影 ID星星评级日期 <正文>20110122011-01-2220110142011-01-272021064空20310322011-01-2020310842011-01-1220310822011-01-3020410132011-01-0920510332011-01-2720510422011-01-222051084空20610732011-01-1520610652011-01-1920710752011-01-2020810432011-01-02

reviewer表

<表类=“s-表”><标题>评论者ID姓名 <正文>201莎拉·马丁内斯202丹尼尔刘易斯203布列塔尼·哈里斯204迈克·安德森205克里斯· jackson 206伊丽莎白·托马斯207詹姆斯·卡梅隆208阿什利·怀特

预期结果

<表类=“s-表”><标题>审阅者标题 <正文>莎拉·马丁内斯随风而逝

编辑:我正在使用 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

https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=0c5d850ee3393b054d9af4c4ac241d96

关键部分是EXISTS语句

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 

这将仅返回同一用户拥有多于一部电影的结果，根据 ratingDate 评分星星比上一个更大