MySQL объединяет четыре таблицы и использует WEEK и DATEDIFF для получения текущих данных?

Я пытаюсь написать запрос, получая информацию из четырех таблиц, и я не могу понять это. Может кто-нибудь, пожалуйста, помогите мне с некоторыми рекомендациями?

Модель заключается в том, что пользователь может забронировать номер на определенный период времени. Я хотел бы получить актуальные данные за следующие семь дней, поэтому я использую WEEK и DATEDIFF. DATEDIFF должен убедиться, что я не получу информацию о бронировании номеров в будущем или более года назад. У меня есть четыре таблицы: пользователи, комнаты, время, бронирование

ПОЛЬЗОВАТЕЛИ:

  CREATE TABLE IF NOT EXISTS users (
  apartmentNumber varchar(4) NOT NULL,
  surname varchar(50) NOT NULL,
  password varchar(40) NOT NULL,
  PRIMARY KEY  (apartmentNumber)
)

НОМЕРА:

CREATE TABLE IF NOT EXISTS rooms (
  id int(11) NOT NULL auto_increment,
  name varchar(40) NOT NULL,
  PRIMARY KEY  (id),
  KEY name (name)
)

РАЗ:

CREATE TABLE IF NOT EXISTS times (
  id int(11) NOT NULL auto_increment,
  start time NOT NULL,
  stop time NOT NULL,
  PRIMARY KEY  (id),
  KEY start (start)
)

БРОНИРОВАНИЕ:

CREATE TABLE IF NOT EXISTS bookings (
  id int(11) NOT NULL auto_increment,
  user varchar(4) NOT NULL,
  room int(11) NOT NULL,
  date date NOT NULL,
  time int(11) NOT NULL,
  PRIMARY KEY  (id),
  KEY room (room),
  KEY time (time),
  KEY user (user)
)

Я заполнил эти таблицы следующими данными:

INSERT INTO bookings (id, user, room, date, time) VALUES
(1, '0379', 1, '2009-11-19', 1),
(2, '0379', 1, '2009-11-23', 2),
(3, '0379', 1, '2009-11-14', 3),
(4, '0379', 2, '2009-11-23', 3),
(5, '0379', 2, '2009-11-19', 2),
(6, '0379', 2, '2009-11-20', 1);

ALTER TABLE bookings
  ADD CONSTRAINT bookings_ibfk_2 FOREIGN KEY (room) REFERENCES rooms (id) ON DELETE CASCADE ON UPDATE CASCADE,
  ADD CONSTRAINT bookings_ibfk_3 FOREIGN KEY (user) REFERENCES users (apartmentNumber) ON DELETE CASCADE ON UPDATE CASCADE,
  ADD CONSTRAINT bookings_ibfk_4 FOREIGN KEY (time) REFERENCES times (id) ON DELETE CASCADE ON UPDATE CASCADE;

INSERT INTO rooms (id, name) VALUES
(1, 'Room 1'),
(2, 'Room 2');

INSERT INTO times (id, start, stop) VALUES
(1, '07:00:00', '12:00:00'),
(2, '12:00:00', '17:00:00'),
(3, '17:00:00', '22:00:00');

INSERT INTO users (apartmentNumber, surname, password) VALUES
('0379', 'Smith', 'password');

Перед добавлением таблицы времен (поскольку время ранее было в таблице заказов) мне удалось получить данные с помощью следующего запроса:

SELECT users.surname, users.apartmentNumber, rooms.name as room, bookings.date, bookings.time 
FROM bookings, rooms, users
WHERE WEEK(NOW(), 7) = WEEK(bookings.date, 7)
AND DATEDIFF(NOW(), bookings.date) < 1
AND DATEDIFF(NOW(), bookings.date) > -10
AND users.apartmentNumber = bookings.user
AND bookings.room = rooms.id
ORDER BY room, bookings.time, bookings.date ASC;

Но после того, как я выбил время в отдельную таблицу, я не могу этого сделать:

SELECT users.surname, users.apartmentNumber, rooms.name as room, bookings.date, times.start, times.stop
FROM bookings, rooms, users, times
WHERE WEEK(NOW(), 7) = WEEK(bookings.date, 7)
AND DATEDIFF(NOW(), bookings.date) < 1
AND DATEDIFF(NOW(), bookings.date) > -10
AND users.apartmentNumber = bookings.user
AND bookings.room = rooms.id
AND times.id = bookings.time
ORDER BY room, bookings.time, bookings.date ASC;

Набор результатов из приведенного выше запроса:

Smith       379  Room 2  2009-11-20  07:00:00  12:00:00

но он должен включать два бронирования на 23 ноября 2009 г.

Я потерялся ... любые комментарии будут оценены. Спасибо!

/Андерс


mysql join datediff
person Community    schedule 20.11.2009    source источник

Ответы (2)


arrow_upward
1
arrow_downward

Использование вами функции НЕДЕЛЯ() ограничивает события ЭТОЙ неделей, а 2009-11-23 приходится на СЛЕДУЮЩУЮ неделю. Это несовместимо с вашим заявленным

получить данные, которые являются текущими в течение следующих семи дней

Я не полностью проанализировал ваш запрос, но думаю, вам следует поэкспериментировать с изменением диапазона функции DATEDIFF() с (-10,1) на (-10,+7) и вообще удалить НЕДЕЛЮ.

Пара побочных комментариев:

  1. Вы должны использовать более современный синтаксис JOIN, который легче читать.
  2. укажите свой диапазон дат, используя BETWEEN
person Jim Garrison    schedule 20.11.2009

arrow_upward
0
arrow_downward

Проблема с использованием WEEK() заключается в том, что он дает фиксированное 7-дневное окно, а не скользящее 7-дневное окно, которое, как вам кажется, вам нужно. Попробуй это:

SELECT users.surname, users.apartmentNumber, rooms.name as room, bookings.date, times.start, times.stop
FROM bookings, rooms, users, times
WHERE bookings.date >= CURDATE()
AND bookings.date <= DATE_ADD(CURDATE(), INTERVAL 7 DAY)
AND users.apartmentNumber = bookings.user
AND bookings.room = rooms.id
AND times.id = bookings.time
ORDER BY room, bookings.time, bookings.date ASC;

В зависимости от того, хотите ли вы, чтобы он был инклюзивным/эксклюзивным и что именно вы подразумеваете под неделей, вам может потребоваться настроить крайние случаи (например, > вместо >= или INTERVAL 8 DAY).

person Rob Van Dam    schedule 16.12.2009