SELECT
   W.name,
   M.title
FROM
   reviewer AS R
   INNER JOIN movie AS M
      ON EXISTS ( -- there is at least one rating
         SELECT *
         FROM rating AS G
         WHERE
            -- by the reviewer and movie in question
            R.rID = G.rID
            AND M.mID = G.mID
            AND EXISTS ( -- for which another rating exists
               SELECT *
               FROM rating AS G2
               WHERE
                  -- for the same reviewer and movie
                  R.rID = G2.rID
                  AND M.mID = G2.mID
                  AND G.stars < G2.stars -- but rated higher
                  AND G.ratingDate < G2.ratingDate -- and later
            )
      )
;

我不是100%确定SQLite是否允许ON子句包含EXISTS表达式。如果没有，您可以将EXISTS表达式移动到WHERE子句中，并在reviewer和movie之间执行交叉连接。
如果SQLite不支持EXISTS，则将EXISTS查询作为派生表放在FROM子句中，其中两个表INNER JOIN彼此相连，然后GROUP BYmId和rID，然后INNER JOIN到主表。它可能看起来像这样：

SELECT
   R.name,
   M.title
FROM
   (
      SELECT
         G.rID,
         G.mID
      FROM
         rating AS G
         INNER JOIN rating AS G2
            ON G.rID = G2.rID
            AND G.mID = G2.mID
            AND G.stars < G2.stars
            AND G.ratingDate < G2.ratingDate
      GROUP BY
         G.rID,
         G.mID
   ) C
   INNER JOIN reviewer AS R
      ON C.rID = R.rID
   INNER JOIN movie AS M
      ON C.mID = R.mID
;

我希望您能看到这两个查询如何表达相同的语义。在一个非常大的数据库中，人们对同一部电影进行了多次评级，可能会有性能差异（我首先展示的EXISTS版本可以表现得更好，因为它可以在找到一个结果时立即停止）。
注意：您可以将整个混乱的查询连接到一个查询中，然后GROUP BYname，title，rID，mID，但是虽然“更简单”，但这将是更错误的，因为不需要为许多行复制名称和标题，只需要通过分组丢弃这些信息。分组应尽早进行。

我已经成功地解决了这个特定的练习与此查询：

SELECT R.name, M.title
FROM
    Rating AS RatingLatest
JOIN Rating AS R2 
    ON RatingLatest.rID = R2.rID AND R1.mID = R2.mID
JOIN Reviewer AS R USING (rID)
JOIN Movie AS M USING (mID)
-- Check if there is a newer rating with more stars than the previous one 
WHERE RatingLatest.ratingDate > R2.ratingDate 
AND RatingLatest.stars > R2.stars

它返回评论者对同一部电影进行过多次评级的地方，以及最后一次（不具体是第二次）评级更高的时间。

也试试这个：

select yes1.name,yes1.title from (select yes. name, yes. title,d.stars,d.rating date,(rank() over(partition by yes.name order by d.stars desc)) as rankk1,(rank() over(partition by yes.name order by d.ratingDate asc )) as rankk2
from (select c.mID,a.rID,a.name,c.title from Reviewer as a join Rating as b on a.rID=b.rID
join Movie as c on b.mID=c.mID group by a.name,c.title,c.mID,a.riD having count(*)=2) as yes join Rating as d on yes.mID = d.mID and yes.riD = d.rID) as yes1 where yes1.rankk1 = 1 and yes1.rankk2=2;

我遵循了简单的分析方法，得到了正确的答案。