显示MySQL中日期之间的间隔 [英] Show gaps between dates in MySQL
本文介绍了显示MySQL中日期之间的间隔的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
如何显示mysql中的剩余/互补日期?
How can I show the remaining/complementary dates in mysql?
例如,在我的表中有2列,是
的快照
from_date>14-06-2014 and to_date<01-07-2014
For example in my table that has 2 columns, a snapshot for
from_date>14-06-2014 and to_date<01-07-2014
将给出以下输出:
From date || To_date
15-06-2014 || 20-06-2014
23-06-2014 || 27-06-2014
29-06-2014 || 30-06-2014
我希望能够显示我们之间有间隔并且没有记录的日期,如下所示:
I would like to be able to show the dates that we have a gap and no records exist, like this:
2 //21-06-2014 - 23-06-2014
1 //28-06-2014
这可能吗? 谢谢
推荐答案
以及这些行:
drop table if exists dates;
create table dates (d_from date, d_to date);
insert into dates values
('2014-06-15' , '2014-06-20'),
('2014-06-23' , '2014-06-27' ),
('2014-06-29' , '2014-06-30' );
select low.d_to, high.d_from, to_days(high.d_from) - to_days(low.d_to) - 1 as gap
from dates low, dates high
where high.d_from = (select min(d_from) from dates where d_from > low.d_to)
;
这意味着:在相邻的结束日期/开始日期将表与其自身连接,然后计算差异.
Which means: join the table to itself on adjacent end/start dates and compute the difference.
+------------+------------+------+
| d_to | d_from | gap |
+------------+------------+------+
| 2014-06-20 | 2014-06-23 | 2 |
| 2014-06-27 | 2014-06-29 | 1 |
+------------+------------+------+
这篇关于显示MySQL中日期之间的间隔的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文