在选择查询中根据年龄拉取不同的行

Question

上下文

我使用的是Oracle SQL，数据如下所示。人们每年都会被问到他们最喜欢的电影是什么，这可能会逐年更新。

Person        Date_of_Birth        Favorite_Movie      Update_Date
Bob             08-10-1950            Endgame             2021-01-01
Bob             08-10-1950            Batman              2020-01-01
Peter           11-01-1990            Toy Story           2021-01-01
Peter           11-01-1990            Mulan               2020-01-01

问题

我想写一个查询，这样：

if DOB >= 70 then pull the Favorite Movie from the year they turned 70.
else: extract movie from most current year 

因此，对于Peter和Bob来说，期望的结果看起来是：

Person            DOB            Favorite_Movie      Update_Date
Bob             08-10-1950            Batman              2020-01-01
Peter           11-01-1990            Toy Story           2021-01-01

当前代码

我当前的查询如下：

SELECT *,
CASE 
WHEN trunc(months_between(sysdate,Date_Of_Birth)/12) >= 70 THEN

Answer 1

一种方法使用相关的子查询：

select f.*
from favorites f
where f.update_date = (select max(f2.update_date)
from favorites f2
where f2.person = f.person and
f2.date_of_birth >= add_months(f.update_date, - 12 * 70)
);

Answer 2

如果按年龄(70岁时排名最高)对行进行排序，然后更新日期(这样最近的一年就会排名第一)，那么这可能是一个选项：

SQL> select person, dob, favorite_movie, update_date
2  from (select t.*,
3        row_number() over
4          (partition by person
5           order by case when extract(year from update_date) - extract (year from dob) = 70 then 1 else 2 end,
6                    update_date desc) rn
7        from test t
8       )
9  where rn = 1;
PERSO DOB        FAVORITE_ UPDATE_DAT
----- ---------- --------- ----------
Bob   1950-10-08 Batman    2020-01-01
Peter 1990-01-11 Toy Story 2021-01-01
SQL>