Mysql找到缺少的日期范围 [英] Mysql Find missing date ranges
本文介绍了Mysql找到缺少的日期范围的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
我有一个包含日期范围的表格：
创建表d（
date_start date，
date_end date
）;
插入d值（'2014-03-05'，'2014-04-10'）;
insert into d values（'2014-05-01'，'2014-06-05'）;
insert into d values（'2014-07-10'，'2014-08-15'）;
我想在2014年完成这个缺少日期范围的表格。在这种情况下， / p>
2014-01-01 - 2014-03-04
2014-04-11 - 2014-04-30
2014-06-06 - 2014-07-09
2014-08-16 - 2014-12-31
任何mysql查询建议？
编辑
更好地使用这些值：
创建表d（
date_start date，
date_end date
）;
插入d值（'2014-06-01'，'2014-06-30'）;
insert into d values（'2014-07-01'，'2014-08-03'）;
insert into d values（'2014-09-01'，'2014-09-30'）;
编辑2
我几乎在这里：
SELECT
date_start，
date_end
FROM
（SELECT
ends.point AS date_start，
starts.point AS date_end
FROM
（SELECT
.date_end + INTERVAL 1天as point，
@n：= @ n + 1 AS num
FROM
d
CROSS JOIN（SELECT @n：= 1）AS init0
ORDER BY date_start
）AS结束
INNER JOIN
（SELECT
d.date_start-INTERVAL 1天，
@m：= @ m + 1 AS num
FROM
d
CROSS JOIN（SELECT @m：= 0）AS init1
ORDER BY date_start
）AS启动
ON ends.num = starts.num
UNION ALL
SELECT
'2014-01-01'，
MIN（date_start） - INTERVAL 1 DAY
FROM
d
WHERE
date_start> ='2014-01-01'
UNION ALL
SELECT
MAX（date_end）+ INTERVAL 1天，
'2014-12-31 '
FROM
d
WHERE
date_end< ='2014-12-31'
）as dates
WHERE
date_start< date_end
ORDER BY
date_start;但是，对于以下间隔集，此查询返回错误的结果：
创建表d（date_start日期，date_end日期）;
插入d值（'2014-01-01'，'2014-01-09'）;
insert into d values（'2014-01-10'，'2014-03-15'）;
插入d值（'2014-03-16'，'2014-04-20'）;
insert into d values（'2014-05-01'，'2014-07-30'）;
insert into d values（'2014-08-01'，'2014-09-30'）;
insert into d values（'2014-12-25'，'2014-12-31'）;
似乎它不能处理单日，如2014-07-31 - 2014-07-31。 / p>
解决方案如果你的日期间隔不会被嵌套或相交，你可以使用
JOIN以生成结果集。因此，要选择所需的记录集，您需要：SELECT
*
FROM
（SELECT
ends.point AS date_start，
starts.point AS date_end
FROM
（SELECT
d.date_end + INTERVAL 1天，
@n：= @ n + 1 AS num
FROM
d
CROSS JOIN（SELECT @n：= 1）AS init0
）AS结束
INNER JOIN
（SELECT
d.date_start-INTERVAL 1天，
@m：= @ m + 1 AS num
FROM
d
CROSS JOIN SELECT @m：= 0）AS init1
）AS启动
ON ends.num = starts.num
UNION ALL
SELECT
'2014-01-01' ，
MIN（date_start） - INTERVAL 1天作为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 dates
WHERE
date_start< = date_end
ORDER 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查询。I have a table with date ranges:
create table d ( date_start date, date_end date ); insert into d values('2014-03-05', '2014-04-10'); insert into d values('2014-05-01', '2014-06-05'); insert into d values('2014-07-10', '2014-08-15');I want to complete this table with missing date ranges in 2014. In this case that would be:
2014-01-01 - 2014-03-04 2014-04-11 - 2014-04-30 2014-06-06 - 2014-07-09 2014-08-16 - 2014-12-31Any mysql query suggestions?
Edit
Better use these values:
create table d ( date_start date, date_end date ); insert into d values('2014-06-01', '2014-06-30'); insert into d values('2014-07-01', '2014-08-03'); insert into d values('2014-09-01', '2014-09-30');Edit 2
I am almost there with this:
SELECT date_start, date_end 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 ORDER BY date_start ) 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 ORDER BY date_start ) AS starts ON ends.num=starts.num UNION ALL SELECT '2014-01-01', MIN(date_start) - INTERVAL 1 DAY FROM d WHERE date_start>='2014-01-01' UNION ALL SELECT MAX(date_end)+INTERVAL 1 DAY, '2014-12-31' FROM d WHERE date_end <= '2014-12-31' ) as dates WHERE date_start < date_end ORDER BY date_start;However, this query returns wrong results for the following intervals set:
create table d (date_start date, date_end date); insert into d values('2014-01-01', '2014-01-09'); insert into d values('2014-01-10', '2014-03-15'); insert into d values('2014-03-16', '2014-04-20'); insert into d values('2014-05-01', '2014-07-30'); insert into d values('2014-08-01', '2014-09-30'); insert into d values('2014-12-25', '2014-12-31');It seems it cannot handle single days like 2014-07-31 - 2014-07-31.
解决方案In case if your date intervals will not be nested or intersecting, you can use trick with
JOINto produce result set. So, to select desired record set, you'll need: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 dates WHERE date_start<=date_end ORDER BY date_start;that will result in
+------------+------------+ | 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 | +------------+------------+(fiddle for this is here)
To "complete" your table you may use
INSERT..SELECTsyntax withSELECTquery above.这篇关于Mysql找到缺少的日期范围的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
