Проект: Я работаю над проектом, который касается некоторых комнат и оборудования, используемого в комнатах. Программное обеспечение предназначено для планирования оборудования в комнатах. Другими словами, это программное обеспечение для резервирования, которое резервирует выбранное оборудование в отдельных комнатах для необходимых дат и интервалов времени. У меня много таблиц в базе данных MYsSQL, работающих с Php, но я расскажу о таблицах, о которых идет речь. В таблицах я расскажу о таблице оборудования (таблица A), таблице расписания (таблица B) и оборудовании, используемом в соответствующем расписании (таблица C).

Таблица A: таблица списка оборудования

eqid | eqName | available| 1 | book | 90 | 2 | pen | 82 | 3 | computer | 25 | 

В таблице А; eqid представляет собой уникальный идентификатор оборудования, eqName представляет название оборудования, доступное представляет собой общее существующее существующее оборудование.

Таблица B: таблица расписания

 scheduleid | startDate | endDate | startTime | endTime | office | 1 | 2012-08-27 | 2012-08-27 | 08:30:00 | 10:00:00 | room1 | 2 | 2012-08-27 | 2012-08-27 | 09:30:00 | 11:00:00 | room3 | 3 | 2012-08-28 | 2012-08-30 | 08:30:00 | 12:00:00 | room2 | 4 | 2012-08-29 | 2012-08-31 | 11:30:00 | 14:00:00 | room1 | 5 | 2012-08-28 | 2012-08-28 | 10:30:00 | 14:00:00 | room3 | 6 | 2012-08-27 | 2012-08-30 | 08:30:00 | 10:00:00 | room4 | 7 | 2012-08-27 | 2012-08-27 | 10:30:00 | 12:00:00 | room4 | 8 | 2012-08-27 | 2012-08-30 | 08:30:00 | 11:00:00 | room6 | 9 | 2012-08-27 | 2012-08-27 | 10:30:00 | 12:00:00 | room5 | 

В таблице B; schedid представляет собой уникальный идентификатор для расписания, startDate и endDate – это диапазон дат для расписания, времени начала и окончания времени для расписания, офис означает, что в случае расписания. Позвольте привести здесь пример. Scheduleid 1 означает, что бронирование производится 27 августа 2012 года, в понедельник, и с 08:30 до 10:00. Поскольку он начинается и заканчивается в тот же день, это всего лишь однодневное бронирование в номере1. Тем не менее, Scheduleid 3 означает, что бронирование начинается 28 августа 2012 года, вторник и продолжается до 30 августа 2012 года, в четверг в 08:30 – 12:00 … другими словами, он длится 3 дня и каждый день с С 08:30 до 12:00 … Итак, есть бронирование со вторника по четверг с 08:30 до 12:00 в номере2 … Надеюсь, это ясно.

Таблица C: использование оборудования в соответствующем графике

 Autoid | scheduleid | eqid | amountInSch| 1 | 1 | 1 | 2 | 2 | 1 | 2 | 3 | 3 | 1 | 3 | 1 | 4 | 2 | 1 | 1 | 5 | 2 | 2 | 1 | 6 | 2 | 3 | 2 | 7 | 3 | 2 | 1 | 8 | 3 | 3 | 3 | 9 | 4 | 2 | 1 | 10 | 4 | 3 | 1 | 11 | 5 | 1 | 1 | 12 | 6 | 1 | 1 | 13 | 6 | 3 | 2 | 14 | 6 | 2 | 4 | 15 | 7 | 1 | 5 | 16 | 7 | 2 | 6 | 17 | 8 | 2 | 1 | 18 | 9 | 1 | 8 | 19 | 9 | 2 | 5 | 20 | 9 | 3 | 6 | 

В таблице C: Autoid представляет собой уникальный автоматический идентификатор, сгенерированный автоматическим приращением, schedid – из таблицы B, eqid – из таблицы A, amountInSch представляет, сколько (количества) оборудования будет использовать в соответствующем расписании. Я хочу привести здесь пример. Таблица 1 в таблице C содержит 3 строки. Это означает, что в таблице 1, связанной с таблицей 1, связанной с TAble B, будут использоваться две книги (eqid 1), 3 ручки (eqid 2) и 1 компьютер (eqid 3) в комнате1, указанные даты и время в таблице B. Другим примером является то, что schedid 3 в таблице C относится к двум строкам. Это означает, что 1 ручка (eqId 2) и 3 компьютера (eqId 3) будут использоваться в комнате2 с 27 по 30 августа 2012 года каждый день с 08:30 до 12:00.

Вышеприведенное объяснение и дать некоторую информацию о проекте. Строки таблицы не являются постоянными. Когда вы сделаете оговорку, в таблице B появится новая строка, и если будет выбрано оборудование, в таблице C появятся новые строки …

Вопрос:

Я хочу рассчитать левую сумму определенного оборудования, когда я поставлю eqId, startDate, endDate, startTime и endTime …

Пример:

eqId: 1 (книга)

startDate: 2012-08-27

endDate: 2012-08-27

startTime: 08:30:00

endTime: 12:00:00

Результат должен быть: 14 книг, использованных в расписании, и 76 оставшихся доступных книг

Потому что: если вы посмотрите schedIds и связанные с ним eqIds, вы увидите только 1, 2, 6, 7, 9 scheduleIds, связанные с моим запросом (даты и eqId). Если вы суммируете всю сумму, связанную с таблицей C, вы получите неверный результат. Другими словами, соответствующие суммы для eqId (1-book) и для 1, 2, 6, 7, 9 scheduleIds равны 2, 1, 1, 5, 8 соответственно. Поэтому, если вы их суммируете, вы получите 17, что неверно. Поскольку расписания 1 и 9 не пересекаются друг с другом с точки зрения времени начала и конца, а 6 и 7 не пересекаются друг с другом. в результате чего они остаются одинокими и могут учитываться отдельно. Мы должны рассматривать 1 и 9 как суммированные 8, потому что 8 больше 2. Это одинаково для 6 и 7, считающихся 5 из-за 5 больше 1 …

Так что люди! Я не уверен, как это можно суммировать в алгоритме программирования. Есть ли способ сделать в SQL или мне нужно использовать PHP и Mysql вместе? и как?

Ура!

Записи SQLFiddle

 SELECT MAX(available) - IFNULL(SUM(amountInSch), 0) FROM Table1 LEFT JOIN Table3 USING (eqid) LEFT JOIN Table2 USING (scheduleid) WHERE DATE(startDate) <= '2012-08-27' AND DATE(endDate) >= '2012-08-27' AND endTime > '08:30' AND startTime < '12:00' AND eqid = 1