MySQL查找缺少的日期范围

MySQL查找缺少的日期范围

如果您的日期间隔不会被嵌套或相交，则可以使用把戏与

JOIN

产生结果集。因此，要选择所需的记录集，您需要：

SELECt  *FROM(SELECt   ends.point AS date_start,   starts.point AS date_end FROM   (SELECt     d.date_end+INTERVAL 1 DAY AS point,     @n:=@n+1 AS num   FROM     d       CROSS JOIN (SELECt @n:=1) AS init0  ) AS ends   INNER JOIN   (SELECT     d.date_start-INTERVAL 1 DAY AS point,     @m:=@m+1 AS num   FROM     d       CROSS JOIN (SELECt @m:=0) AS init1  ) AS starts   ON ends.num=starts.num UNIOn ALL   SELECT     '2014-01-01',     MIN(date_start) - INTERVAL 1 DAY AS date_end  FROM     d   HAVINg     date_end>'2014-01-01' UNIOn ALL   SELECt     MAX(date_end)+INTERVAL 1 DAY AS date_start,     '2014-12-31'   FROM    d   HAVINg     date_start<'2014-12-31' ) as datesWHERe  date_start<=date_endORDER BY   date_start;

这将导致

+ ------------ + ------------ +| date_start | date_end |+ ------------ + ------------ +| 2014-01-01 | 2014-03-04 || 2014-04-11 | 2014-04-30 || 2014-06-06 | 2014-07-09 || 2014-08-16 | 2014-12-31 |+ ------------ + ------------ +

（这是小提琴在这里）

要“完成”表，您可以

INSERT..SELECT

在

SELECT

上面的查询中使用语法。