如何匹配starts_at周年纪念日在指定区间且排除ends_at的记录？

我们需要从subscriptions表中筛选出符合以下两个条件的记录：

• 字段starts_at的任意周年纪念日落在指定时间区间内
• 字段ends_at的所有周年纪念日都不在该指定时间区间内

示例表结构与数据 #

subscriptions表的结构及测试数据如下：

示例场景 #

指定时间区间为2015-12-01至2016-02-01，此时应返回ID为2的记录：

• 其starts_at的周年纪念日2016-01-01落在区间内
• 其ends_at的周年纪念日2017-01-01不在该区间内

解决方案SQL #

以下是适用于MySQL的查询语句，核心通过计算日期的年偏移匹配周年纪念日：

SELECT *
FROM subscriptions
WHERE 
  -- 检查starts_at是否存在落在区间内的周年纪念日
  EXISTS (
    SELECT 1
    FROM (
      SELECT ADDDATE(starts_at, INTERVAL n YEAR) AS anniversary
      FROM (
        SELECT 0 AS n UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5
        -- 根据实际数据年份范围调整n的取值，覆盖可能的周年数
      ) AS years
    ) AS annivs
    WHERE anniversary BETWEEN '2015-12-01' AND '2016-02-01'
  )
  AND 
  -- 检查ends_at的所有周年纪念日都不在区间内
  NOT EXISTS (
    SELECT 1
    FROM (
      SELECT ADDDATE(ends_at, INTERVAL n YEAR) AS anniversary
      FROM (
        SELECT 0 AS n UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5
      ) AS years
    ) AS annivs
    WHERE anniversary BETWEEN '2015-12-01' AND '2016-02-01'
  );

逻辑说明 #

1. starts_at检查：生成starts_at的多个周年日期（通过添加不同年数的时间间隔），只要其中有一个落在指定区间内，即满足条件。
2. ends_at检查：同样生成ends_at的多个周年日期，确保没有任何一个落在指定区间内，才满足条件。
3. 注意：子查询中的年份范围（0到5）需要根据实际数据调整，确保覆盖所有可能存在的周年纪念日情况。

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