基于日期字段查询各职位当前生效费率的SQL实现方案

基础数据 #

测试用费率表数据如下：

ID JOB_ID   RATE      START_DT     END_DT
43  41       11      06/06/2022 
42  42       12      06/06/2022 
41  43       10      06/06/2022   06/17/2022
62  43       15      06/17/2022   06/21/2022
63  43       18.5    06/21/2022   06/22/2022
65  43       19.5    06/22/2022 
44  45       15      06/06/2022 
45  46       16      06/06/2022 
46  47       19      06/06/2022 
47  48       20      06/06/2022 
48  49       25      06/06/2022 

规则说明 #

• 记录的END_DT为空时，代表该费率从START_DT开始永久生效，无失效时间
• 同一JOB_ID可配置多段连续的时间分段费率，后一段起始时间衔接前一段结束时间
• 直接使用sysdate between start_dt and end_dt作为条件，无法匹配END_DT为空的永久生效记录，也无法一次性返回所有职位的对应生效费率

预期返回结果 #

1. 查询日期为2022年6月11日时，返回结果：

ID JOB_ID   RATE      START_DT     END_DT
43  41       11      06/06/2022 
42  42       12      06/06/2022 
41  43       10      06/06/2022   06/17/2022
44  45       15      06/06/2022 
45  46       16      06/06/2022 
46  47       19      06/06/2022 
47  48       20      06/06/2022 
48  49       25      06/06/2022 

2. 查询日期为次年（晚于所有分段费率结束时间）时，返回结果：

ID JOB_ID   RATE      START_DT     END_DT
43  41       11      06/06/2022 
42  42       12      06/06/2022 
65  43       19.5    06/22/2022 
44  45       15      06/06/2022 
45  46       16      06/06/2022 
46  47       19      06/06/2022 
47  48       20      06/06/2022 
48  49       25      06/06/2022 

实现方案 #

核心逻辑：对每个职位，筛选出所有**生效起始时间早于等于查询日期，且（结束时间晚于查询日期/无结束时间）**的候选记录，取其中生效起始时间最晚的一条，就是查询当日的生效费率。

写法1：窗口函数实现（支持Oracle、MySQL8.0+、PostgreSQL等主流数据库） #

SELECT ID, JOB_ID, RATE, START_DT, END_DT
FROM (
    SELECT 
        t.*,
        ROW_NUMBER() OVER (
            PARTITION BY JOB_ID 
            ORDER BY START_DT DESC
        ) AS rn
    FROM job_rate t -- 替换为实际业务表名
    WHERE 
        -- 替换为实际查询日期，取当前日期可直接用sysdate（Oracle）/CURDATE()（MySQL）
        START_DT <= TO_DATE('2022-06-11', 'yyyy-mm-dd')
        AND (END_DT > TO_DATE('2022-06-11', 'yyyy-mm-dd') OR END_DT IS NULL)
)
WHERE rn = 1;

写法2：关联子查询实现（兼容不支持窗口函数的老版本数据库） #

SELECT t.*
FROM job_rate t -- 替换为实际业务表名
WHERE 
    START_DT <= TO_DATE('2022-06-11', 'yyyy-mm-dd')
    AND (END_DT > TO_DATE('2022-06-11', 'yyyy-mm-dd') OR END_DT IS NULL)
    AND NOT EXISTS (
        SELECT 1 
        FROM job_rate t2
        WHERE 
            t2.JOB_ID = t.JOB_ID
            AND t2.START_DT <= TO_DATE('2022-06-11', 'yyyy-mm-dd')
            AND (t2.END_DT > TO_DATE('2022-06-11', 'yyyy-mm-dd') OR t2.END_DT IS NULL)
            AND t2.START_DT > t.START_DT
    );

逻辑验证 #

• 查询日期为2022年6月11日时，JOB_ID=43仅ID=41的记录满足候选条件，返回费率10
• 查询日期为次年时，JOB_ID=43仅ID=65的永久生效记录满足候选条件，返回费率19.5
• 其余无分段费率的职位，始终返回唯一的永久生效记录，完全匹配预期结果

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