MySQL：结合COUNT子查询与EXCEPT查询，正确计算人员剩余可用时间

数据表结构 #

• crew_availability（别名ca）：
  • id(INT)
  • admin_id(INT)
  • usable_date(DATE)
• 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;

但同一人员单日哪怕仅预订1次，EXCEPT会直接排除该日期的可用记录，无法体现剩余的1次可用额度。

尝试的无效查询（硬编码特定ID和日期，逻辑错误）：

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

期望结果 #

• 若admin_id在某日期的预订数达到2次，不返回该日期的可用记录；
• 若admin_id在某日期仅预订1次，返回该日期的可用记录；
• 若admin_id在某日期无预订，返回该日期的可用记录。

测试数据 #

crew_availability 插入语句 #

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

reservations 插入语句 #

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在2023-04-10有2个预订，不应返回该日期的可用记录；
• admin_id=22在2023-04-17有1个预订，需返回该日期的可用记录；
• 无预订的日期（如admin_id=1的2023-04-03）需返回可用记录。

正确查询语句 #

方法1：LEFT JOIN + 分组统计 #

SELECT ca.admin_id, ca.usable_date
FROM crew_availability ca
LEFT JOIN (
    SELECT admin_id, ride_date, COUNT(*) AS booking_count
    FROM reservations
    WHERE admin_id IS NOT NULL
    GROUP BY admin_id, ride_date
) AS r_stats ON ca.admin_id = r_stats.admin_id AND ca.usable_date = r_stats.ride_date
WHERE r_stats.booking_count < 2 OR r_stats.booking_count IS NULL;

方法2：NOT EXISTS + 分组筛选 #

SELECT ca.admin_id, ca.usable_date
FROM crew_availability ca
WHERE NOT EXISTS (
    SELECT 1
    FROM reservations r
    WHERE r.admin_id = ca.admin_id AND r.ride_date = ca.usable_date
    GROUP BY r.admin_id, r.ride_date
    HAVING COUNT(*) >= 2
);

说明 #

• 两种方法均会排除单日预订数≥2的记录，保留预订数<2或无预订的日期；
• 过滤reservations中admin_id为NULL的记录，避免干扰统计逻辑；
• 测试数据中admin_id=2的2023-04-03、admin_id=28的2023-04-03均有2次预订，会被正确排除。

内容的提问来源于stack exchange，提问作者RetroRocker