查找后续电影的较低评级

Question

我有一个表、电影、和另一个名为directors的表，它具有以下属性：

Movies               Director
------              -----------
title               name
director            country
year
genre
rating

我正在试图找到同一导演的电影，它的评分低于任何后续的电影从同一导演。这是我的疑问：

SELECT * 
FROM movies m1
JOIN directors d ON m1.director=d.name 
WHERE EXISTS
    (
    SELECT * 
    FROM movies m2 
    WHERE (m1.rating<m2.rating 
    AND m1.year<m2.year)
    AND m1.director=m2.director
    );

我期望得到的结果如下：

movie1    director1    rating-2   year1
movie2    director1    rating-4   year2
movie3    director1    rating-2   year4
movie4    director1    rating-4   year7

然而，相反，我得到了：

movie1    director1    rating 2   year1
movie2    director1    rating 4   year2
movie3    director1    rating-4   year4
movie4    director1    rating-2   year7

我不认为这是正确的做法。:(

Answer 1

你想要一个当地的最低标准。。。但恰恰相反。

select m.*
from (select m.*,
             min(m.rating) over (partition by m.director order by m.year desc rows between unbounded preceding and 1 preceding) as min_future_rating
      from movies m
     ) m
where rating < min_future_rating;