时间戳

首页 > 解决方案 > Mysql - 连接表必须匹配所有日期的条件

mysql - Mysql - 连接表必须匹配所有日期的条件

问题描述

我有两张桌子：resources和booking_sums。我想获得在某个日期范围内，每个日期的所有可用时间段都已订满的资源。但这被证明是非常困难的。

我有一个booking sums表，其中存储日期和每个日期的三个时间段 ( event_time)。

booking_sums
--------------
    event_date               (date)
    event_time               (1-3)
    resource id
    sum_booked
    free_seats_left

我提供一个event date范围，并获得满足此条件的资源：

我的问题是，如果只有一个日期符合条件，我仍然可以获得资源。

所以如果我有这些booking_sums：

id  |  event_date  |   event_time  |  resource_id  |  sum_booked  |  free_seats_left  |
id  |  2019-09-18  |        1      |      122      |      250     |        0    |
id  |  2019-09-18  |        2      |      122      |      250     |        0    |
id  |  2019-09-19  |        1      |      122      |      250     |        0    |
id  |  2019-09-19  |        2      |      122      |      250     |        0    |
id  |  2019-09-19  |        3      |      122      |      250     |        0    |

日期 2019-09-19 符合条件，但 2019-09-18 不符合条件。因此，如果 2019-09-18 包含在日期范围内，我们不应该得到该结果 - 但我仍然得到它。这是我的查询：

SELECT distinct r.* FROM resources r
INNER JOIN bookings_sums bs1
ON r.id = bs1.resource_id  
WHERE EXISTS(
      SELECT 1 FROM bookings_sums 
      where event_time = 1
      AND resource_id = r.id 
      AND event_date = bs1.event_date 
      AND free_seats_left = 0
    )
AND EXISTS(  SELECT 1 FROM bookings_sums where event_time = 2 AND resource_id = r.id 
       AND event_date = bs1.event_date AND free_seats_left = 0)
AND EXISTS(  SELECT 1 FROM bookings_sums where event_time = 3 AND resource_id = r.id 
       AND event_date = bs1.event_date AND free_seats_left = 0) 

AND bs1.event_date BETWEEN '2019-09-18' AND '2019-09-19'

编辑：这是一个小提琴：sqlfiddle

Edit2：从下面建议的答案中，我做了一些调整以使其更简单，这似乎有效：

 SELECT r.id, 
        r.title
  FROM resources r
  INNER JOIN bookings_sums bs     
    ON r.id = bs.resource_id 
    AND bs.resource_id = r.id 
    AND bs.event_date 
    BETWEEN '2019-09-19' AND '2019-09-19' AND bs.free_seats_left = 0
  GROUP BY r.id, r.title
  HAVING COUNT(bs.id) = (DATEDIFF('2019-09-19', '2019-09-19') + 1) * 3

（而不是 DATEDIFF 我可以使用 php 并在我的情况下直接输入一个数字）

标签： mysql

解决方案

您应该能够在没有子查询的情况下做到这一点。

SELECT r.*
FROM resources r
INNER JOIN bookings_sums bs1
ON r.id = bs1.resource_id
AND bs1.resource_id = r.id
AND bs1.event_date = bs1.event_date
AND bs1.free_seats_left = 0
GROUP BY r......
HAVING SUM(bs.id) = 3
AND bs1.event_date BETWEEN '2019-09-18' AND '2019-09-19'

您需要在 GROUP BY 查询中指定资源表上的所有列。

或者只是做 3 个连接

SELECT r.* 
FROM resources r
INNER JOIN bookings_sums bs1     ON r.id = bs1.resource_id  AND bs1.event_time = 1 AND bs1.resource_id = r.id AND bs1.free_seats_left = 0
INNER JOIN bookings_sums bs2     ON r.id = bs2.resource_id  AND bs2.event_time = 2 AND bs2.resource_id = r.id AND bs2.event_date = bs1.event_date AND bs2.free_seats_left = 0
INNER JOIN bookings_sums bs3     ON r.id = bs3.resource_id  AND bs3.event_time = 3 AND bs3.resource_id = r.id AND bs3.event_date = bs1.event_date AND bs3.free_seats_left = 0
WHERE bs1.event_date BETWEEN '2019-09-18' AND '2019-09-19'

编辑

这给了你你需要的东西，但必须有一个更整洁的方法来做到这一点。

SELECT id,
        title
FROM
(
  SELECT r.id, 
        r.title , 
        sub0.day_cnt
  FROM resources r
  CROSS JOIN
  (
    SELECT DATEDIFF('2019-09-19', '2019-09-18') + 1 AS day_cnt
  ) sub0
  INNER JOIN bookings_sums bs     ON r.id = bs.resource_id AND bs.resource_id = r.id AND bs.event_date BETWEEN '2019-09-18' AND '2019-09-19' AND bs.free_seats_left = 0
  GROUP BY r.id, r.title
  HAVING COUNT(bs.id) = sub0.day_cnt * 3
) sub1;

推荐阅读