MYSQL：范围匹配与周年纪念日

发布于08月09日

我正在try 获取starts_at个列周年纪念中的任何一个与范围期间匹配的记录，但不是与ends_at个周年纪念日匹配的记录.

例如，我们有这样一张subscriptions张桌子:

id	starts_at	ends_at
1	2015-01-01	2016-01-01
2	2014-01-01	2017-01-01

范围在2015-12-01到2016-02-01之间.

我需要ID: 2的，因为它的一个周年纪念日(2016-01-01)在范围内，而ends_at不在范围内.

SQLFiddle个

推荐答案

创建一个年份查找表，如下所示，并根据您的数据(比如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');

然后，使用年份查找来加入订阅表，以查找所有可能的周年纪念.最后，根据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