想要一些逻辑上的帮助,制定一个MYSQL查询,得到的结果不是在表的数据。
我有一个名为schedule的表,其中包含数据类型为"time"的列,该列指示此计划何时开始和结束,以及来自表"rooms"的外键引用,其中计划将在其中发生。在其搜索功能的php代码中,我想添加一个功能,显示当前没有被时间表占用或空置的房间的结果。我添加了一个jquery滑块来获取搜索者想要的开始时间和结束时间。
TABLE 'schedule'
room sched_start sched_end
1 09:00:00 10:00:00
1 11:00:00 12:00:00
2 07:30:00 08:30:00
2 11:30:00 13:00:00
例如,搜索者想要搜索10:00:00到11:00:00之间的空房。根据数据库,结果应该显示两个房间,房间1和房间2,应该显示在搜索结果中,因为两个房间在搜索者指定的时间内不会被占用。我在考虑按时间顺序比较所有类似房间的时间表,第一行或第一个时间表的"sched_end"和后面一行或时间表的sched_start等等,以便确定两者之间是否有空闲时间。有人能帮我一下吗?
所有的帮助和仇恨将非常感激,因为我可以在MySQL-ing一样多的新手。
DROP TABLE IF EXISTS schedule;
CREATE TABLE schedule
(room INT NOT NULL
,schedule_start TIME NOT NULL
,schedule_end TIME NOT NULL
,PRIMARY KEY(room,schedule_start)
);
INSERT INTO schedule VALUES
(1,'09:00:00','10:00:00'),
(1,'11:00:00','12:00:00'),
(2,'07:30:00','08:30:00'),
(2,'11:30:00','13:00:00'),
(3,'09:30:00','10:30:00'),
(3,'11:00:00','12:00:00'),
(4,'10:30:00','10:45:00');
SET @start:= '10:00:00';
SET @end:= '11:00:00';
SELECT DISTINCT x.room
-- or whatever columns you want from whichever table you want
FROM schedule x
LEFT
JOIN schedule y
ON y.room = x.room
AND y.schedule_start < @end
AND y.schedule_end > @start
-- other tables can join in here
WHERE y.room IS NULL;
+------+
| room |
+------+
| 1 |
| 2 |
+------+
http://sqlfiddle.com/!9/1b677/1
只是为了证明@M0rtiis的解决方案是错误的…
SELECT DISTINCT room
FROM schedule
WHERE @end <= schedule_start
OR @start >= schedule_end;
+------+
| room |
+------+
| 1 |
| 2 |
| 3 |
+------+
您需要的是明确排除在给定时间段内被占用的房间。
SET @start = '10:00:01';
SET @end = '10:59:59';
SELECT *
FROM `schedule` -- you probably want to select from rooms here...
WHERE room NOT IN (
SELECT room
FROM `schedule`
WHERE sched_start BETWEEN @start AND @end
OR sched_end BETWEEN @start AND @end
OR @start BETWEEN sched_start AND sched_end
OR @end BETWEEN sched_start AND sched_end
);
请注意,我通过在开始时间上增加一秒,在结束时间上减去一秒来补偿"包含开始"的行为。您应该在将时间提供给SQL之前这样做,以避免那里的计算。
该查询过滤所有情况,包括重叠的会议。
或者,可能更连贯一点:
SET @start:= '10:00:00';
SET @end:= '11:00:00';
SELECT DISTINCT room
FROM schedule
WHERE room NOT IN ( SELECT room
FROM schedule
WHERE schedule_start < @end
AND schedule_end > @start );
另外,如果这个查询要执行的不仅仅是几行,那么您确实需要适当的索引。使用EXPLAIN
函数来帮助您
把TIME存储在那里是个坏主意。使用DATETIME来代替need_start -一天和need_end -另一天(下一个?)或者我想在你的酒店当客人一个星期?)天。
但是无论如何,在你现在拥有的东西上尝试这个
SELECT DISTINCT
room
FROM schedule
WHERE
'11:00:00' <= sched_start
OR
'10:00:00' >= sched_end
http://sqlfiddle.com/!9/dafae/9
可以使用BETWEEN
运算符
SELECT *
FROM schedule
WHERE sched_end BETWEEN '10:00:00' AND '11:00:00'