MySQL:如何将COUNT子查询的结果与EXCEPT查询组合

pxq42qpu  于 2023-04-29  发布在  Mysql
关注(0)|答案(2)|浏览(140)

我有两张table。..

  1. crew_availability(ca),具有id(INT)、admin_id(INT)和usable_date(DATE)字段。
  2. reservations(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在预订表中不是唯一的,并且即使只有一个机组成员的游乐设施具有给定日期的预订,其对于该天任何其他游乐设施的剩余可用性不再反映在EXCEPT查询的结果中。
我试图通过计算一个乘务员在给定的一天中乘坐的次数来解决这个问题,如果少于总数,则在主EXCEPT查询结果e中仍然反映他们的剩余可用性。g的。

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);

这在语法上可能可行,但它不起作用。也许它能概括出我想达到的目标
任何帮助试图实现一个正确的代表可用性将是真诚的感谢,谢谢
crew_availability 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');

关于预期的结果,举个例子。..

  1. admin_id 1,此admin_id在10/04/2023对两个可能的ride_id有2个保留,因此,查询不应返回任何行,因为它们已被充分利用。
  2. admin_id 22,此admin_id有1关于ride_id 2的预订,在17/04/2023,因此ride_id 1在该日期仍然是免费的,crew_availability应该仍然返回一行(但没有)
    1.在任何一天具有两个ride_id空闲的任何admin_id将具有来自crew_availability的单行的预期返回。
hlswsv35

hlswsv351#

这是你可能的答案。检查查询。它应该会给予你结果

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;
x6yk4ghg

x6yk4ghg2#

进一步的工作导致了对代码的重大修改,删除了使用SELECT COUNT的想法,我转而将保留表本身加入。经过大量测试后,该查询似乎完全满足所有3个要求的结果。..

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
);

相关问题