mysql - MySQL错误计数

Question

我的 MySQL 查询错误地计算了一个值。

我想计算为某个活动预留的位置以及正在进行预订的位置数量。

如果我在单独的查询中进行每个计数，它可以完美地工作，但是当我将它们组合起来时，第二个只使用第一个的结果。

-- --------------------------------------------------------

--
-- Table structure for table `eventtimeslots`
--

CREATE TABLE `eventtimeslots` (
  `id` int(11) NOT NULL,
  `event_id` int(11) NOT NULL,
  `date_start` date NOT NULL,
  `date_end` date NOT NULL,
  `time_start` time DEFAULT NULL,
  `time_end` time DEFAULT NULL,
  `max_participants` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Dumping data for table `eventtimeslots`
--

INSERT INTO `eventtimeslots` (`id`, `event_id`, `date_start`, `date_end`, `time_start`, `time_end`, `max_participants`) VALUES
(1, 1, '2020-09-04', '2020-09-04', '17:00:00', '17:30:00', 6),
(2, 1, '2020-09-04', '2020-09-04', '17:30:00', '18:00:00', 6),
(3, 1, '2020-09-04', '2020-09-04', '17:30:00', '18:00:00', 7),
(4, 2, '2020-09-05', '2020-09-05', '17:00:00', '17:30:00', 6),
(5, 2, '2020-09-05', '2020-09-05', '17:30:00', '18:00:00', 6),
(6, 3, '2020-09-06', '2020-09-06', '17:30:00', '18:00:00', 6);

-- --------------------------------------------------------

--
-- Table structure for table `reservation`
--

CREATE TABLE `reservation` (
  `id` int(11) NOT NULL,
  `user_id` int(11) NOT NULL,
  `reservation_hash` varchar(32) NOT NULL,
  `date` date NOT NULL,
  `time` time NOT NULL,
  `event_id` int(11) NOT NULL,
  `time_slot_id` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Dumping data for table `reservation`
--

INSERT INTO `reservation` (`id`, `user_id`, `reservation_hash`, `date`, `time`, `event_id`, `time_slot_id`) VALUES
(1, 1, '8d42ca883c91e1c117da98907fdd33cb', '2020-08-27', '17:00:00', 1, 1),
(2, 1, '8d42ca883c91e1c117da98907fdd33cb', '2020-08-27', '17:00:00', 2, 4),
(3, 1, '8d42ca883c91e1c117da98907fdd33cb', '2020-08-27', '17:00:00', 3, 6),
(4, 2, 'e1794aca0a47186e291756a38ca8d198', '2020-08-27', '17:00:00', 1, 1),
(5, 2, 'e1794aca0a47186e291756a38ca8d198', '2020-08-27', '17:00:00', 2, 4),
(6, 3, '40ce5675639354a46de2b07d1bc1aeee', '2020-08-27', '17:00:00', 1, 3),
(7, 3, '40ce5675639354a46de2b07d1bc1aeee', '2020-08-27', '17:00:00', 2, 4),
(8, 3, '40ce5675639354a46de2b07d1bc1aeee', '2020-08-27', '17:00:00', 3, 6);

-- --------------------------------------------------------

--
-- Table structure for table `temp_reservation`
--

CREATE TABLE `temp_reservation` (
  `id` int(11) NOT NULL,
  `user_id` int(11) NOT NULL,
  `reservation_token` varchar(32) NOT NULL,
  `event_id` int(11) NOT NULL,
  `time_slot_id` int(11) NOT NULL,
  `date_start` date NOT NULL,
  `date_end` date NOT NULL,
  `time_start` time NOT NULL,
  `time_end` time NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Dumping data for table `temp_reservation`
--

INSERT INTO `temp_reservation` (`id`, `user_id`, `reservation_token`, `event_id`, `time_slot_id`, `date_start`, `date_end`, `time_start`, `time_end`) VALUES
(8, 1, 'c219b9bd7c716f0e8ca111f27f705f36', 1, 1, '2020-09-04', '2020-09-04', '17:00:00', '17:30:00'),
(9, 1, 'c219b9bd7c716f0e8ca111f27f705f36', 2, 5, '2020-09-05', '2020-09-05', '17:30:00', '18:00:00'),
(10, 1, 'c219b9bd7c716f0e8ca111f27f705f36', 3, 6, '2020-09-06', '2020-09-06', '17:30:00', '18:00:00');

--
-- Indexes for dumped tables
--

--
-- Indexes for table `eventtimeslots`
--
ALTER TABLE `eventtimeslots`
  ADD PRIMARY KEY (`id`);

--
-- Indexes for table `reservation`
--
ALTER TABLE `reservation`
  ADD PRIMARY KEY (`id`);

--
-- Indexes for table `temp_reservation`
--
ALTER TABLE `temp_reservation`
  ADD PRIMARY KEY (`id`);

--
-- AUTO_INCREMENT for dumped tables
--

--
-- AUTO_INCREMENT for table `eventtimeslots`
--
ALTER TABLE `eventtimeslots`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=7;

--
-- AUTO_INCREMENT for table `reservation`
--
ALTER TABLE `reservation`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=9;

--
-- AUTO_INCREMENT for table `temp_reservation`
--
ALTER TABLE `temp_reservation`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=11;
COMMIT; 

http://sqlfiddle.com/#!9/b65bbe6/4

SELECT 
    eventtimeslots.*, 

    reservation.time_slot_id, 
COUNT(reservation.time_slot_id) AS places_reserved, 

    temp_reservation.time_slot_id, 
COUNT(temp_reservation.time_slot_id) AS places_temp_reserved 

FROM eventtimeslots 
LEFT JOIN reservation ON eventtimeslots.id = reservation.time_slot_id 
LEFT JOIN temp_reservation ON eventtimeslots.id = temp_reservation.time_slot_id 
WHERE eventtimeslots.date_start >= NOW() 
GROUP BY eventtimeslots.id;

标签： mysql