使用mysql查找日期差距 [英] Find date gaps with mysql
问题描述
我想提取一个预订编号为空的记录,并从(第一个免费日开始)获得最多的未预订天数.预期结果应为:
I would like to extract records with having an empty bookingId and get the maximum unbooked days back (from the first free day). The expected result should be:
id = 1, 2013-08-03, 7 days free
id = 1, 2013-08-24, 7 days free
id = 2, 2013-08-07, 10 days free
id = 2, 2013-08-24, 7 days free
最好的办法是,如果我还可以查询一个空闲时隙:查询1,2,3,4,5,6,7..14 ..空闲日.这是我的源数据的一个示例:
The best thing would be, If I can also query for a free timeslot: e.g. query for 1,2,3,4,5,6,7..14.. free days. This is an example of my source data:
id bookingDate bookingId
--------------------------------
1 2013-08-03 0
1 2013-08-04 0
1 2013-08-05 0
1 2013-08-06 0
1 2013-08-07 0
1 2013-08-08 0
1 2013-08-09 0
1 2013-08-10 112
1 2013-08-11 112
1 2013-08-12 112
1 2013-08-13 112
1 2013-08-14 112
1 2013-08-15 112
1 2013-08-16 112
1 2013-08-17 112
1 2013-08-18 112
1 2013-08-19 112
1 2013-08-20 112
1 2013-08-21 112
1 2013-08-22 112
1 2013-08-23 112
1 2013-08-24 0
1 2013-08-25 0
1 2013-08-26 0
1 2013-08-27 0
1 2013-08-28 0
1 2013-08-29 0
1 2013-08-30 0
1 2013-08-31 0
2 2013-08-03 78
2 2013-08-04 78
2 2013-08-05 78
2 2013-08-06 78
2 2013-08-07 0
2 2013-08-08 0
2 2013-08-09 0
2 2013-08-10 0
2 2013-08-11 0
2 2013-08-12 0
2 2013-08-13 0
2 2013-08-14 0
2 2013-08-15 0
2 2013-08-16 0
2 2013-08-17 39
2 2013-08-18 39
2 2013-08-19 39
2 2013-08-20 39
2 2013-08-21 39
2 2013-08-22 39
2 2013-08-23 39
2 2013-08-24 0
2 2013-08-25 0
2 2013-08-26 0
2 2013-08-27 0
2 2013-08-28 0
2 2013-08-29 0
2 2013-08-30 0
2 2013-08-31 0
如果任何人都有更好的数据结构的好主意,我可以尝试实现.该数据库仍在建设中:-)
If anyone has an good idea for a better data structure, I can try to implement. The database is still under construction :-)
CREATE TABLE IF NOT EXISTS `pricesBookings` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`baseId` int(11) NOT NULL,
`bookingDate` date NOT NULL,
`bookingId` int(11) NOT NULL,
`price` decimal(10,2) NOT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `baseId` (`baseId`,`bookingDate`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
推荐答案
这应该给出正确的结果:
This should give the correct result:
select
id,
min(startDate) as startFreeDate,
count(*) - (endDate is null) numFreeDays
from (
select
pb1.id,
pb1.bookingDate startDate,
min(pb2.bookingDate) endDate
from
pricesBookings pb1 left join pricesBookings pb2
on pb1.id=pb2.id
and pb2.price>0
and pb2.bookingDate>pb1.bookingDate
where
pb1.price=0
group by
pb1.id,
pb1.bookingDate
) s
group by id, endDate
order by id, startDate
在此处查看.
如果您需要搜索所有空闲时段(例如14天),则可以添加HAVING:
If you need to search for all free slots of, for example, 14 days, you can add HAVING:
group by id, endDate
having count(*) - (endDate is null) >= 14
order by id, startDate
这篇关于使用mysql查找日期差距的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!