SQL调度系统

Question

我想要建立一个考试计划系统。现在，我的目标是避免用户在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 Fiddle

MS 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