验证mysql行是否与另一行冲突

I'm making a resource reservation system on the school I work in. Resources are reserved for only 40min (which is what the class lasts).

我正在我所在的学校建立资源预订系统。资源只保留40分钟(这是课程的持续时间)。

Resources are Split into 4 categories:

资源分为4类:

1- Preschool

2- Elementary

3- High School, First Floor

一楼3-高中

4- High School, Second Floor

二楼4-高中

---

Schedules are devided into different time spans:

时间表分为不同的时间跨度:

1- nursery

2- pre-kinder

3- kinder

4- elementary (1st grade - 3rd grade)

4-小学(1年级 - 3年级)

5- elementary (4th grade - 6th grade)

5-小学(4年级 - 6年级)

6- High School (7th grade - 9th grade)

6-高中(7年级 - 9年级)

7- High School (10th grade and up)

7-高中(10年级及以上)

Sometimes, there's a scenario where 1st grade to 3rd grade students are in break, and 4th grade to 6th grade students are in a class. Break lasts 15min and classes 40min. Sometimes, there are conflicts on schedules where if the user is allowed to reserve a resource, that resource would be in use for at least another 10 minutes by another user, this happens on both preschool, elementary and high school.

有时候,有一年级到三年级的学生休息,四年级到六年级的学生都在上课。休息持续15分钟,课程40分钟。有时候,如果允许用户保留资源,那么在时间表上存在冲突,该资源将被另一个用户使用至少另外10分钟,这发生在学前班,小学和高中。

My DB reservation table structure is as follows:

我的数据库预订表结构如下:

I have two fields that I think will help me determine whether making a reservation will make a conflict or not: RESERVATIONDUEDATE and RESERVATIONEXPIREDATE which are both datetime and have the info I need. I'm having trouble picturing how would I make a query to verify if there will be a conflict or not.

我有两个字段,我认为这将有助于我确定预订是否会产生冲突:RESERVATIONDUEDATE和RESERVATIONEXPIREDATE既是日期时间又有我需要的信息。我无法想象如何进行查询以验证是否存在冲突。

So far, I have a function with the query that looks like this:

到目前为止,我有一个查询函数,如下所示:

     SELECT T1.SCHEDULE_START, T1.SCHEDULE_END
FROM reservation T4
JOIN schedule_intermediate T1 ON T4.SCHEDULEID_GENERATED = T1.SCHEDULEID_GENERATED
JOIN schedule_type T2 ON T1.SCHEDULE_TYPE_ID = T2.SCHEDULE_TYPE_ID
JOIN schedule T3 ON T1.SCHEDULEID = T3.SCHEDULEID
WHERE (
SELECT COUNT( * ) 
FROM reservation R
INNER JOIN schedule_intermediate SI ON R.SCHEDULEID_GENERATED = SI.SCHEDULEID_GENERATED
WHERE R.RESOURCEID =456
AND SI.SCHEDULE_TYPE_ID =4
AND R.RESERVATIONDUEDATE !=  '2014-08-06 07:35:00'
AND R.RESERVATIONEXPIREDATE !=  '2014-08-06 08:35:00'
AND(( SI.SCHEDULE_START < T1.SCHEDULE_START AND SI.SCHEDULE_END > T1.SCHEDULE_START ) 
    OR ( SI.SCHEDULE_START < T1.SCHEDULE_END AND SI.SCHEDULE_END > T1.SCHEDULE_END ))
) = 0

That query intends to select only the available schdule periods of time where the RESOURCEID, SCHEDULE_TYPE_ID, 'SCHEDULE_START' and 'SCHEDULE_END' are not the same as the info provided in the query.

该查询旨在仅选择RESOURCEID,SCHEDULE_TYPE_ID,'SCHEDULE_START'和'SCHEDULE_END'与查询中提供的信息不同的可用时间段。

At this point, I'm not even sure if the query is structured correctly, as the result of this query is:

此时,我甚至不确定查询是否结构正确,因为此查询的结果是:

And what I really want are the periods of time where those properties are not the same as the ones provided.

而我真正想要的是这些属性与提供的属性不同的时间段。

1 个解决方案

#1

I'm trying to get my head around what you need. Try this... do a search similar to what your doing...

我正试图让你的头脑满足你的需要。试试这个...做一个类似于你做的搜索......

WHERE R.RESOURCEID =456
AND SI.SCHEDULE_TYPE_ID =4
AND R.RESERVATIONDUEDATE >=  '2014-08-06 07:35:00'
AND R.RESERVATIONEXPIREDATE <=  '2014-08-06 08:35:00'

If your query returns 0 rows, there will be no *es.

如果您的查询返回0行,则不会发生冲突。

At the moment, your query returns that row because the Schedule_Start is 07:50 which does not equal 07:35:00 ?

目前,您的查询返回该行,因为Schedule_Start是07:50,不等于07:35:00?

Also, is this for recurring sessions, and if so.. how are you managing this in your DB in the timestamp format?

此外,这是重复会话,如果是这样..你如何在时间戳格式的数据库中管理这个?