Oracle SQL查询：匹配CS_START_DATE获取正确START_PERIOD.ID并校验SP_ID

问题背景 #

两张表结构及需求如下：

• START_PERIOD表（存储日期区间与对应ID）：

• APPLICATION_FORM表：包含CS_START_DATE（始终正确的日期字段）和SP_ID（原本应匹配START_PERIOD.id，但当前关联关系出错）

需求：通过CS_START_DATE匹配START_PERIOD的日期范围获取正确ID，与现有SP_ID对比，找出所有不匹配的记录。

解决方案SQL #

SELECT 
    af.*,
    sp.id AS correct_sp_id,
    sp.start_date AS correct_period_start,
    sp.end_date AS correct_period_end
FROM 
    APPLICATION_FORM af
LEFT JOIN 
    START_PERIOD sp 
    ON af.CS_START_DATE BETWEEN sp.start_date AND sp.end_date
WHERE 
    af.SP_ID != sp.id
    OR sp.id IS NULL;

语句解释 #

1. 关联逻辑：使用LEFT JOIN将APPLICATION_FORM与START_PERIOD关联，匹配条件为CS_START_DATE落在目标区间内，从而得到该日期对应的正确START_PERIOD.id。
2. 筛选条件：
   • af.SP_ID != sp.id：直接对比现有SP_ID与计算出的正确ID，筛选出不相等的记录。
   • sp.id IS NULL：覆盖两种异常情况：SP_ID无对应START_PERIOD记录，或SP_ID对应的区间不包含CS_START_DATE。
3. 返回字段：返回APPLICATION_FORM全量字段及正确的区间信息，便于直接对比排查错误。

注意事项 #

若START_PERIOD存在重叠日期区间，需结合业务规则补充过滤条件（如优先匹配最新区间、特定类型区间等），确保每个CS_START_DATE仅匹配唯一正确的START_PERIOD记录。

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