SQL多行间值校验：按业务规则关联日期获取对应描述值

表结构与数据 #

Table1（包含id和date列） #

id        date
         101       04-07-2018
         102       15-11-2018
         103       25-01-2019
         104       28-06-2019

Table2（包含start date、end date和value列） #

start date    end date     value
         29-11-2016    28-11-2017   A
         29-11-2017    29-11-2018   B
         30-11-2018    15-03-2019   C
         16-03-2019    29-06-2019   D
         30-06-2019    31-11-2021   E

业务规则 #

• 若date落在某个区间内，且该区间的下一个连续区间（下一个区间的start date为当前区间end date加1天）的start date与date的间隔≤30天，则取下一个区间的value；
• 若间隔>30天，或当前区间是最后一个区间，则取当前区间的value；
• 示例：
  • id101的date（2018-07-04）落在B区间，距离C区间的start date（2018-11-30）超过30天，对应值为B；
  • id102的date（2018-11-15）落在B区间，距离C区间的start date仅15天，对应值为C；
  • id104的date（2019-06-28）落在D区间，距离E区间的start date仅2天，对应值为E。

期望输出结果 #

需输出包含id与desc两列的结果表：

id      desc
          101     B
          102     C
          103     C
          104     E

实现SQL（以MySQL为例） #

SELECT 
    t1.id,
    CASE 
        -- 判断当前日期到下一个区间起始日的间隔是否≤30天
        WHEN DATEDIFF(STR_TO_DATE(t2_next.`start date`, '%d-%m-%Y'), STR_TO_DATE(t1.date, '%d-%m-%Y')) <= 30 THEN t2_next.value
        ELSE t2.value
    END AS `desc`
FROM 
    Table1 t1
-- 关联找到date所在的当前区间
JOIN 
    Table2 t2 ON STR_TO_DATE(t1.date, '%d-%m-%Y') BETWEEN STR_TO_DATE(t2.`start date`, '%d-%m-%Y') AND STR_TO_DATE(t2.`end date`, '%d-%m-%Y')
-- 关联当前区间的下一个连续区间（起始日为当前区间结束日+1天）
LEFT JOIN 
    Table2 t2_next ON STR_TO_DATE(t2_next.`start date`, '%d-%m-%Y') = DATE_ADD(STR_TO_DATE(t2.`end date`, '%d-%m-%Y'), INTERVAL 1 DAY)
ORDER BY 
    t1.id;

说明 #

1. 使用STR_TO_DATE将字符串格式的日期（dd-mm-yyyy）转换为MySQL可识别的日期类型；
2. 通过JOIN找到date所属的当前区间，再通过LEFT JOIN关联其下一个连续区间；
3. 利用DATEDIFF计算日期间隔，结合CASE语句判断最终取值；
4. 若当前区间是最后一个区间，t2_next会为NULL，此时直接取当前区间的value。

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