MySQL: как объединить результат подзапроса COUNT с запросом EXCEPT
У меня 2 стола...
- экипаж_доступность (ca), который имеет поля id(INT), admin_id(INT) и usable_date(DATE).
- бронирование (r), которое имеет поля id(INT), ride_id(INT), admin_id(INT) и ride_date(DATE).
Следующий запрос EXCEPT дает ожидаемый результат, поскольку он дает доступность оставшегося экипажа.
SELECT ca.admin_id, ca.usable_date FROM crew_availability ca EXCEPT SELECT r.admin_id, r.ride_date FROM reservations r;
Однако проблема возникает в том, что для одного члена экипажа в день существует более одной поездки (поле ride_id в таблице бронирования). Это, конечно, означает, что admin_id/ride_date не являются уникальными в таблице бронирований, и даже если только одна из поездок члена экипажа имеет бронирование на определенную дату, их оставшаяся доступность для любых других поездок в этот день больше не отражается в результате. ЗАПРОС ИСКЛЮЧЕНИЕ.
Я попытался решить эту проблему, подсчитав количество поездок, которые член экипажа совершил в данный день, и, если меньше общего возможного, чтобы их оставшаяся доступность по-прежнему отражалась в основном результате запроса ЗА ИСКЛЮЧЕНИЕМ, например.
SELECT ca.admin_id, ca.usable_date FROM crew_availability ca EXCEPT SELECT r.admin_id, r.ride_date FROM reservations r
WHERE (SELECT COUNT(id) FROM reservations r WHERE r.admin_id = 1 AND r.ride_date = '2023-04-10' < 2);
Это может быть синтаксически жизнеспособным, но это не работает... возможно, это служит для определения того, чего я пытаюсь достичь.
Будем искренне признательны за любую помощь в попытке добиться правильного представления о доступности, спасибо.
экипаж_доступность SQL:
SET NAMES 'utf8mb4';
INSERT INTO `bike-data`.crew_availability(id, admin_id, usable_date, created_at, updated_at) VALUES
(42, 28, '2023-04-03', '2022-08-21 15:43:45', '2022-08-21 15:43:45'),
(50, 1, '2023-04-03', '2022-08-21 17:02:51', '2022-08-21 17:02:51'),
(53, 1, '2023-04-10', '2022-08-21 17:59:58', '2022-08-21 17:59:58'),
(55, 1, '2023-04-17', '2022-08-22 15:34:43', '2022-08-22 15:34:43'),
(56, 1, '2023-04-24', '2022-08-22 16:26:33', '2022-08-22 16:26:33'),
(64, 22, '2023-04-03', '2023-02-07 16:18:00', '2023-02-07 16:18:00'),
(65, 22, '2023-04-10', '2023-02-07 16:18:29', '2023-02-07 16:18:29'),
(69, 2, '2023-04-03', '2023-03-31 09:01:50', '2023-03-31 09:01:50'),
(70, 22, '2023-04-17', '2023-03-31 09:01:50', '2023-03-31 09:01:50'),
(72, 2, '2023-04-17', '2023-03-31 09:01:50', '2023-03-31 09:01:50'),
(73, 22, '2023-04-24', '2023-03-31 09:01:50', '2023-03-31 09:01:50'),
(78, 2, '2023-04-24', '2023-03-31 09:01:50', '2023-03-31 09:01:50');
резервирование SQL:
SET NAMES 'utf8mb4';
INSERT INTO `bike-data`.reservations(id, cycle_id, ride_id, admin_id, first_name, ride_date, created_at, updated_at) VALUES
(2, 10, 1, 2, 'Test 2', '2023-04-03', '2022-07-27 15:39:54', '2023-04-18 15:07:00'),
(5, 11, 2, 2, 'Mickey', '2023-04-03', '2022-07-30 19:41:50', '2023-04-18 15:07:10'),
(11, 11, 2, 1, 'aa', '2023-04-10', '2022-08-13 15:59:37', '2023-04-19 13:38:54'),
(30, 8, 2, 22, 'GDPR', '2023-04-17', '2022-08-14 09:45:55', '2023-04-18 15:09:22'),
(31, 9, 1, NULL, 'GDPR-2', '2023-04-10', '2022-08-14 09:49:34', '2023-04-18 15:09:50'),
(32, 9, 2, 2, 'GDPR', '2023-04-17', '2022-08-14 09:58:10', '2023-04-18 15:10:29'),
(33, 10, 1, 28, 'GDPR', '2023-04-03', '2022-08-14 10:18:17', '2023-04-19 14:40:54'),
(34, 8, 2, 28, 'Peter', '2023-04-03', '2022-08-15 16:15:28', '2023-04-19 14:40:48'),
(35, 8, 2, 22, 'Peter', '2023-04-24', '2022-08-15 16:27:56', '2023-04-18 15:19:02'),
(36, 2, 1, NULL, 'Peter', '2023-04-17', '2022-08-22 20:19:27', '2023-04-18 15:19:34'),
(37, 3, 1, 1, 'Test', '2023-04-10', '2022-08-23 09:25:57', '2023-04-19 14:16:19'),
(39, 8, 1, NULL, 'Test', '2023-04-24', '2022-08-23 11:07:15', '2023-04-18 15:21:02');
Что касается ожидаемых результатов, возьмем, к примеру...
- admin_id 1 , этот admin_id имеет 2 резервирования для обоих возможных ride_id на 04.10.2023, поэтому в результате запрос не должен возвращать строки, поскольку они полностью используются.
- admin_id 22 , у этого admin_id есть 1 резервирование для ride_id 2 на 17.04.2023, поэтому, поскольку ride_id 1 все еще свободен для них на эту дату, экипаж_доступность все еще должен возвращать строку (но не возвращает)
- любой admin_id, у которого оба ride_id свободны в любой день, будет иметь ожидаемый возврат одной строки из экипажа_доступности.
Ответы 2
Вот ваш возможный ответ. Проверьте запрос. Это должно дать вам результат
SELECT ca.id, ca.admin_id, ca.usable_date
FROM crew_availability ca
LEFT JOIN reservations r ON ca.admin_id = r.admin_id AND ca.usable_date = r.ride_date
WHERE r.ride_id IS NULL AND ca.usable_date = '2023-04-19'
AND (
SELECT COUNT(*) FROM reservations r2 WHERE r2.admin_id = ca.admin_id AND r2.ride_date = ca.usable_date
) < 2;
Поделитесь примером табличных данных и ожидаемым результатом.
Ренука, спасибо. Я обновил исходный вопрос с таблицей SQL x2 и ожидаемыми результатами.
@RetroRocker это тот ответ, который вы ищете? id admin_id usable_date ------ -------- ------------- 70 22 2023-04-17 72 2 2023-04-17
Это не был бы ответ, но это был бы ответ в случае ожидаемого результата номер 2 в исходном вопросе. Он будет возвращен в результирующем наборе вместе с любыми другими доступными admin_ids.
для q1 и 2 это запрос. для q3 вам нужен другой запрос, который не ясен
Давайте продолжим обсуждение в чате.
Ушли болтать...
Дальнейшая работа привела к значительным изменениям в коде: идея использования SELECT COUNT была удалена, вместо этого я присоединил таблицу резервирования к самой себе. Этот запрос после большого количества тестов, по-видимому, полностью удовлетворяет всем трем требуемым результатам...
SELECT ca.id, ca.admin_id, ca.usable_date
FROM crew_availability ca
LEFT JOIN reservations r ON ca.admin_id = r.admin_id AND ca.usable_date = r.ride_date
WHERE r.ride_id IS NULL AND ca.usable_date IN ('2023-04-03', '2023-04-10', '2023-04-17', '2023-04-24')
UNION
(
SELECT r.id, r.admin_id, r.ride_date FROM
reservations r WHERE r.admin_id IS NOT NULL
EXCEPT
SELECT r.id, r.admin_id, r.ride_date FROM
reservations r
INNER JOIN
reservations r1
ON r.admin_id = r1.admin_id AND r.ride_date = r1.ride_date
WHERE r.id <> r1.id
);
Это вероятный шаг в правильном направлении, спасибо... LEFT JOIN вполне может быть предпочтительнее моей попытки EXCEPT. К сожалению, у запроса есть та же проблема, что и у исходного, то есть, если частичная доступность все еще существует, это все равно не отражается в возвращаемом результате. Чтобы было понятнее, r.ride_id не может быть нулевым, он может иметь значение 1 или 2. Я попытался заменить IS NULL на IN (1, 2), но это не дало результатов 😕 Данные для 2 таблиц были проверены и полностью жизнеспособным в том смысле, что он не собирается запутывать проблему сам по себе.