查找后续电影的较低分级

Question

我有一个表movies和另一个名为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;