Блог

У меня есть довольно сложный на вид запрос, который ляжет в основу онлайн-инструмента планирования занятий. Моя задача состоит в том, чтобы разработать метод для определения, на какие классы зарегистрирован пользователь в таблице st_schedule, затем вывести из общей таблицы классов st_classes, какие другие классы доступны, которые не конфликтуют с текущими классами пользователя.

Например, если у пользователя есть запись в st_schedule, назначающая их классу с 8:00 до 9: 00, они не будут иметь права на любой класс, время которого приходится на период с 8:00 до 9:00. Класс, который выполнялся с 7:15 до 8: 15 утра, лишил бы пользователя права. Я сохраняю время начала и окончания занятий в базе данных отдельно для целей сравнения. Важно, чтобы это было как можно более гибким, поэтому концепция времени «блокировки» и назначения времени блокам невозможна.

Вот выдержки из таблиц:

 table st_classes (holds class information)
id
start_time
end_time

table st_schedule (holds schedule information)
id
user_id
class_id
  

Я, конечно, мог бы сделать это в серии циклов на стороне сервера, но я должен думать, что есть метод MySQL, который может выполнить этот тип операции одним махом.

Вы хотите объединить две таблицы вместе, чтобы представить классы пользователя, а затем найти незарегистрированные классы, время начала и окончания которых не совпадает со временем начала и окончания пользовательских классов.

Что-то вроде этого. Полностью неофициально и непроверено:

 SELECT
    *
FROM
    st_schedule s
    INNER JOIN st_classes c ON c.id = s.class_id
    INNER JOIN st_classes all_classes 
        ON all_classes.start_time NOT BETWEEN c.start_time AND c.end_time
        AND all_classes.end_time NOT BETWEEN c.start_time AND c.end_time
WHERE
   s.user_id = 1
  

Редактировать: Попробуйте # 2
У меня есть только мгновение, чтобы взглянуть на это. Я думаю, что я отменил второе предложение join. Псевдоним all_classes представляет полный список классов, где псевдоним «c» представляет классы, на которые зарегистрирован учащийся.

 SELECT DISTINCT
    all_classes.*
FROM
    st_schedule s
    INNER JOIN st_classes c ON c.id = s.class_id
    INNER JOIN st_classes all_classes 
        ON c.start_time NOT BETWEEN all_classes.start_time AND all_classes.end_time
        AND c.end_time NOT BETWEEN all_classes.start_time AND all_classes.end_time
WHERE
   s.user_id = 1
  

Это использует табличные переменные в mssql, но выбранные sql должны быть переведены в mysql

Сначала образец данных

 DECLARE @st_classes TABLE
    (
     ID INT NOT NULL,
     Title VARCHAR(40) NOT NULL,
     StartTime DATETIME NOT NULL,
     EndTime DATETIME NOT NULL
    )       

DECLARE @st_schedule TABLE
    (
     ID INT NOT NULL,
     UserID INT NOT NULL,
     ClassID INT NOT NULL
    )       

INSERT INTO @st_classes (ID, Title, StartTime, EndTime)
SELECT 1,'Class1','08:00:00','09:30:00' UNION 
SELECT 2,'Class2','09:30:00','11:30:00' UNION
SELECT 3,'Class3','11:30:00','16:00:00' UNION
SELECT 4,'Class4','16:00:00','17:30:00' UNION
SELECT 5,'Class5','09:00:00','11:45:00' UNION
SELECT 6,'Class6','07:00:00','18:00:00' 

INSERT INTO @st_schedule(ID, UserID, ClassID)
SELECT 1,1,1 UNION
SELECT 2,1,2 UNION
SELECT 3,2,6
  

Далее немного sql для подтверждения соединения таблиц OK (выбор запланированных курсов для пользователя с идентификатором 1) — Возвращает класс 1 и 2

 SELECT *
FROM    @st_schedule AS S INNER JOIN 
        @st_classes AS C ON S.ClassID = C.ID
WHERE  S.UserID = 1 
  

Теперь нам нужно выбрать все идентификаторы курсов, где они совпадают по времени с запланированными пользователями (включая запланированные) — Возвращает 1,2,5,6

 SELECT  AC.ID
FROM    @st_classes AS AC
        INNER JOIN ( SELECT C.StartTime,
                            C.EndTime
                     FROM   @st_schedule AS S
                            INNER JOIN @st_classes AS C ON S.ClassID = C.ID
                     WHERE  S.UserID = 1
                   ) AS UC ON ( AC.StartTime < DATEADD(ss, -1, UC.EndTime)
                                AND DATEADD(ss, -1, UC.EndTime) > UC.StartTime
                              )
GROUP BY AC.ID 
  

Теперь нам нужно выбрать все курсы, идентификатор курса которых отсутствует в нашем списке совпадающих идентификаторов курсов. — Возвращает курс 3 и 4

 SELECT  *
FROM    @st_classes
WHERE   ID NOT IN (
        SELECT  AC.ID
        FROM    @st_classes AS AC
                INNER JOIN ( SELECT C.StartTime,
                                    C.EndTime
                             FROM   @st_schedule AS S
                                    INNER JOIN @st_classes AS C ON S.ClassID = C.ID
                             WHERE  S.UserID = 1
                           ) AS UC ON ( AC.StartTime < DATEADD(ss, -1, UC.EndTime)
                                        AND DATEADD(ss, -1, UC.EndTime) > UC.StartTime
                                      )
        GROUP BY AC.ID )
  

Измените фильтр идентификатора пользователя на 2, и вы не должны получать никаких возвращений, поскольку курс, назначенный этому пользователю, перекрывает все курсы.