使用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屋！