如何调整SQL查询以获取指定生效日期前的历史数据

现有一张日期追踪表（表名abc），数据如下：

person_number      start_date       end_date            name            position
10                01/01/2016        31/12/4712         Scott            Manager
11                02/01/2006        25/06/2017        Kirk              Associate
11                26/06/2017        18/03/2023        Kirk              Snr. Associate
11                19/03/2023        31/12/4712        Kirk              DIRECTOR

当前使用的查询语句：

select person_number, name, position from abc
where nvl(:p_eff_date,trunc(sysdate)) between start_Date and end_date

需求：

• 传入当日日期时，仅返回当前生效的记录；
• 传入指定生效日期（比如2023年3月1日）时，返回该日期前的所有历史记录（包括在该日期当天仍生效的记录）。

select person_number, name, position, start_date, end_date
from abc
where start_date <= nvl(:p_eff_date, trunc(sysdate))
  and (
        :p_eff_date is null and end_date >= trunc(sysdate)
        or
        :p_eff_date is not null and end_date >= start_date
      )

• 第一个条件start_date <= nvl(:p_eff_date, trunc(sysdate))：先筛选出所有在指定日期（或当前日期）之前就已开始生效的记录，确保不会漏掉任何历史上生效过的条目。
• 第二个条件分场景处理：
  • 当没有传入p_eff_date参数时，只保留end_date >= 当前日期的记录——也就是当前仍在生效的条目，满足“仅返回当前生效”的要求；
  • 当传入了指定生效日期时，只要记录本身是有效的（表中正常记录都会满足end_date >= start_date），就全部保留，这样就能拿到指定日期前的所有历史生效记录。

内容的提问来源于stack exchange，提问作者SSA_Tech124