logo标签列表

MySQL 查找预约之间的空闲时间段

mysql
6
我正在尝试查找一天中的空闲时间段，但是我卡在了正确的查询上。 营业时间从每天9:00到22:00，以下查询返回正确的数据，但如果当天的第一个预订是在10:00，则不会返回9:00到10:00之间未预订的时段；如果从21:30到21:40预订，则不会显示当天最后一个时段的空闲情况。
为了解决这个问题，目前对于每天和set_id，我需要创建一个代表1分钟的日开始和日结束插入条目，分别表示id=1和id=6。
我正在努力避免为每个set_id创建这两个插入项。
CREATE TABLE bookings
(`id` int, `time_from` datetime, `time_to` datetime, `set_id` int);


INSERT INTO bookings
    (`id`, `time_from`, `time_to`, `set_id`)
VALUES
    (1, '2013-11-20 08:59:00', '2013-11-20 09:00:00', 6),
    (2, '2013-11-20 09:10:00', '2013-11-20 10:00:00', 6),
    (3, '2013-11-20 11:10:00', '2013-11-20 11:30:00', 6),
    (4, '2013-11-20 12:00:00', '2013-11-20 12:40:00', 6),
    (5, '2013-11-20 16:20:00', '2013-11-20 16:50:00', 6),
    (6, '2013-11-20 22:00:00', '2013-11-20 22:01:00', 6)
;

SELECT Available_from, Available_to
FROM (
    SELECT @lasttime_to AS Available_from, time_from AS Available_to, @lasttime_to := time_to
    FROM (SELECT time_from, time_to
          FROM bookings
          WHERE set_id = 6
            AND time_to >= '2013-11-20 08:59'
            AND time_from < '2013-11-20 22:01'
      ORDER BY time_from) e
    JOIN (SELECT @lasttime_to := NULL) init) x
WHERE Available_to > DATE_ADD(Available_from, INTERVAL 9 MINUTE);


|      AVAILABLE_FROM |                    AVAILABLE_TO |
|---------------------|---------------------------------|
| 2013-11-20 09:00:00 | November, 20 2013 09:10:00+0000 |
| 2013-11-20 10:00:00 | November, 20 2013 11:10:00+0000 |
| 2013-11-20 11:30:00 | November, 20 2013 12:00:00+0000 |
| 2013-11-20 12:40:00 | November, 20 2013 16:20:00+0000 |
| 2013-11-20 16:50:00 | November, 20 2013 22:00:00+0000 |

非常感谢您的帮助。

http://www.sqlfiddle.com/#!2/b30a0/2

    CREATE TABLE `days` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `date` date NOT NULL,
  `time_from` datetime NOT NULL,
  `time_to` datetime NOT NULL,
  `step` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;


INSERT INTO `days` (`id`, `date`, `time_from`, `time_to`, `step`)
VALUES
    (1, '2013-11-20', '2013-11-20 09:00:00', '2013-11-20 22:00:00', 10),
    (2, '2013-11-21', '2013-11-21 09:00:00', '2013-11-21 22:00:00', 10);

稍微靠近一点！
INSERT INTO `bookings` (`id`, `date`, `time_from`, `time_to`, `set_id`, `name`, `email`, `telephone`, `amount`, `notes`, `is_paid`, `is_booked`)
VALUES
    (25, '2013-11-20', '2013-11-20 09:10:00', '2013-11-20 09:30:00', 1, '', '', '', NULL, NULL, 0, 0),
    (26, '2013-11-20', '2013-11-20 10:30:00', '2013-11-20 11:30:00', 1, '', '', '', NULL, NULL, 0, 0),
    (27, '2013-11-20', '2013-11-20 12:30:00', '2013-11-20 13:20:00', 1, '', '', '', NULL, NULL, 0, 0),
    (29, '2013-11-20', '2013-11-20 15:00:00', '2013-11-20 16:40:00', 1, '', '', '', NULL, NULL, 0, 0);

这是我得到的内容

。

availableFrom         availableTo
9:00                  2013-11-20 09:10:00
2013-11-20 09:30:00       2013-11-20 10:30:00
2013-11-20 11:30:00       2013-11-20 12:30:00
9:00                  2013-11-20 15:00:00
2013-11-20 15:00:00       22:00
谢谢您的回复，我在哪里需要使用联合（union）呢？ - Tim
我有一个包含id、日期、开始时间、结束时间和步骤的days表。 - Tim
所以 id=1，日期=2013年11月20日，时间从=2013年11月20日 09:00:00，时间到=2013年11月20日，步骤=10。 - Tim
该表格现在已添加到问题中！ - Tim
我正在使用的1分钟时段，是为了获取9:00至10:00之间的空白时段。 - Tim
显示剩余3条评论
1个回答
0
一种可能的选择是将表格与自身左连接，类似以下内容：
select (case when ltime_to is null then '9:00' else ltime_to end) as availableFrom,
rtime_from as availableTo
from (select l.time_to as ltime_to, r.time_from as rtime_from from bookings r 
left join bookings l on l.id=r.id-1) lr
union
select max(time_from) as availableFrom, '22:00' as availableTo from bookings
不是那样返回什么。 - Tim
啊，我明白了，一开始没有理解你在做什么。其中一个简单愚蠢的方法是插入一个虚假的预订，从当天的第一时刻开始并结束，然后您可以将其用作第一个time_from。另一个选择是，如果您保证您的表具有不重叠的连续时间段，那么可以在左侧使用自身连接表l.id=r.id-1，然后使用前一个time_from或当天的开始作为time_from。 - Ashalynd
谢谢，虚假条目是插入上面的第一个和最后一个条目 - ID为1和6，您会注意到它们只有1分钟的预订时间。这就是我想要摆脱的，因为我每天都必须为6个场馆或set_id创建这些虚假预订。我认为一定有一种方法可以将开始和结束时间集成到查询中，以便我不必进行虚假插入。（我喜欢虚假这个名字！）我在days表中有开始和结束时间可以使用吗？谢谢 - Tim
其他选项怎么样——将该表与自身进行左连接，但向后移动一行？ - Ashalynd
我编辑了我的回答，并提供了将表格与自身进行左连接的示例。不得不说，我曾经遇到过类似的问题（从一段时间中减去已预订的时间段），但我通过编程解决了它。 - Ashalynd
显示剩余2条评论
网页内容由stack overflow 提供, 点击上面的
可以查看英文原文，
原文链接