SQL调度系统

Question

我想创建一个考试安排系统。 现在，我的目标是避免用户在 SQL Server 2008 中为同一房间插入冲突时间表。 例如：

对于202室

，时间表为

02/27/2017 4:00pm - 5:00pm

那么，如果用户输入的房间 202是

02/27/2017 3:50pm - 5:10pm

显然这是冲突。

如果我要在之间使用显然它不适用于给定的示例输入。

请帮忙。 谢谢

Answer 1

就像尼克说的，between正是你所需要的。在这种情况下，结合exists或not exists，您将能够避免计划冲突。 仅当同一房间、同一天的日程安排中没有条目，且新时间段之间的开始或结束时间(表示冲突)之间没有条目时，才执行insert。

看这个例子：

SQL 小提琴

MS SQL Server 2008 架构设置：

CREATE TABLE schedule
([room] int, [date] date, [start] time, [finish] time)
;
INSERT INTO schedule
([room], [date], [start], [finish])
SELECT  202, '2017-02-26', '16:00', '17:00'
WHERE NOT EXISTS (SELECT * 
FROM schedule 
WHERE room = '202' AND date = '2017-02-26' AND 
(start BETWEEN '16:00' AND '17:00'
OR finish BETWEEN '16:00' AND '17:00'))
;
INSERT INTO schedule
([room], [date], [start], [finish])
SELECT  202, '2017-02-26', '15:50', '17:10'
WHERE NOT EXISTS (SELECT * 
FROM schedule 
WHERE room = '202' AND date = '2017-02-26' AND 
(start BETWEEN '15:50' AND '17:10'
OR finish BETWEEN '15:50' AND '17:10'))
;
INSERT INTO schedule
([room], [date], [start], [finish])
SELECT  202, '2017-02-26', '15:00', '15:59'
WHERE NOT EXISTS (SELECT * 
FROM schedule 
WHERE room = '202' AND date = '2017-02-26' AND
(start BETWEEN '15:00' AND '15:59'
OR finish BETWEEN '15:00' AND '15:59'))
;

查询 1：

SELECT  *
FROM schedule

结果：

| room |       date |            start |           finish |
|------|------------|------------------|------------------|
|  202 | 2017-02-26 | 16:00:00.0000000 | 17:00:00.0000000 |
|  202 | 2017-02-26 | 15:00:00.0000000 | 15:59:00.0000000 |

在应用程序中，使其如下所示：

伪：

SELECT count(*)
FROM schedule 
WHERE room = '202' AND date = '2017-02-26' AND
(start BETWEEN '15:00' AND '15:59'
OR finish BETWEEN '15:00' AND '15:59')
if count(*) = 0 then
INSERT INTO schedule
([room], [date], [start], [finish])
SELECT  202, '2017-02-26', '15:00', '15:59'
else
ERROR "CONFLICT WITH ANOTHER SCHEDULE"
end if