MYSQL:与纪念日匹配的范围

Question

我正在尝试获取任何 starts_at 列周年纪念日与范围周期匹配但与 ends_at 匹配的记录。

例如，我们有这个 subscriptions 表:

<表类="s-表"><头>编号开始于ends_at<正文>12015-01-012016-01-0122014-01-012017-01-01

对于 2015-12-01 到 2016-02-01 的范围。

我需要带有 ID: 2 的那个，因为它的周年纪念日之一 (2016-01-01) 在范围内，但 ends_at不是。

SQLFiddle

Answer 1

创建一个如下所示的年份查找表，并根据您的数据(比如 1980 年到 2050 年)插入行:

CREATE TABLE IF NOT EXISTS `year_lookup` (
  `year_value` int,
  PRIMARY KEY (`year_value`)
) DEFAULT CHARSET=utf8;  

INSERT INTO `year_lookup` (`year_value`) VALUES
  ('2010'),
  ('2011'),
  ('2012'),
  ('2013'),
  ('2014'),
  ('2015'),
  ('2016'),
  ('2017');

然后，使用 yearlookup 加入订阅表以查找所有可能的周年纪念日。最后，根据 starts_at BETWEEN 和 end_at NOT BETWEEN 过滤记录，如下所示。

-- Anniversary
SELECT DISTINCT Final.id FROM (
  SELECT s.id AS id, DATE_ADD(MAKEDATE(y.year_value, 1), INTERVAL DAYOFYEAR(starts_at)-1 DAY) AS starts_at, ends_at 
  FROM subscriptions AS s
  INNER JOIN year_lookup AS y
      ON y.year_value BETWEEN YEAR(starts_at) AND YEAR(ends_at)
  ) AS Final
WHERE    
   ((starts_at BETWEEN '2015-12-01' AND '2016-02-01') 
     AND (ends_at NOT BETWEEN '2015-12-01' AND '2016-02-01')) ; 

引用这个SQL Fiddle