mysql - MySQL查询以找到可用的 parking 位

Question

我有一个卡车停车应用程序，在该应用程序中，卡车运输公司可以预订特定日期的某个位置的停车位数量。

我需要帮助来修改查询以查找特定时期的停车位。

我在此过程中有3张桌子：

位置

+-------------+---------------+
| location_id | location_name |
+-------------+---------------+
|           1 | Parking 1     |
|           2 | Parking 1     |
+-------------+---------------+

插槽

在容量可变的位置包含多个插槽

+---------+-----------+-------------+----------+
| slot_id | slot_name | location_id | capacity |
+---------+-----------+-------------+----------+
|       1 | slot1     |           1 |        5 |
|       2 | slot2     |           1 |        2 |
|       3 | slot-p-2  |           2 |       10 |
+---------+-----------+-------------+----------+

booking_slots

包含每个插槽的预订日期信息和预订的容量

+------------+---------+-------------+--------------+------------+------------+
| booking_id | slot_id | location_id | booking_unit | start_date |  end_date  |
+------------+---------+-------------+--------------+------------+------------+
|          1 |       1 |           1 |            3 | 2019-10-24 | 2019-10-25 |
|          2 |       2 |           1 |            2 | 2019-10-24 | 2019-10-25 |
+------------+---------+-------------+--------------+------------+------------+

我正在使用以下查询。有了这个，我可以在一段时间内获得所有具有当前预订能力的空位：

SELECT slocation_id,
       COALESCE(SUM(bs.booking_unit),0) AS total_booking_unit

FROM   slots s
       INNER JOIN 
       location tc ON tc.location_id = s.location_id
       LEFT JOIN 
       (   SELECT * 
           FROM booking_slots bs 
           WHERE bs.start_date BETWEEN '2019-10-25' AND '2019-10-28' 
                 OR bs.end_date BETWEEN '2019-10-25' AND '2019-10-28'
       ) bs ON bs.slot_id = s.slot_id
GROUP BY s.location_id

我得到以下结果：

+-------------+--------------------+
| location_id | total_booking_unit |
+-------------+--------------------+
|           1 |                  5 |
|           2 |                  0 |
+-------------+--------------------+

但现在我的要求是：

我想消除该时段内所有容量已满的位置。

例如 total_booking_unit应小于该位置的组合插槽容量。

我想到了像 HAVING这样的 total_booking_unit < slot_capacity子句，但是我找不到实际的 SUM(capacity)，因为它总是根据 slot_id表中的 tbl_booking_slots行而变得两倍或三倍。

我该如何实施？我是否需要更改表结构以实现此目的？

还是MySql中有任何选项，这样我可以获取某个位置的组合插槽容量？

Answer 1

让我们尝试一下：

SELECT * FROM location l 
LEFT JOIN slots s ON l.location_id=s.location_id 
LEFT JOIN booking_slots b ON s.location_id=b.location_id AND s.slot_id=b.slot_id;

上面的查询返回示例中的所有信息：

+-------------+---------------+---------+-----------+-------------+----------+------------+---------+-------------+--------------+------------+------------+
| location_id | location_name | slot_id | slot_name | location_id | capacity | booking_id | slot_id | location_id | booking_unit | start_date |  end_date  |
+-------------+---------------+---------+-----------+-------------+----------+------------+---------+-------------+--------------+------------+------------+
|           1 | Parking 1     |       1 | slot1     |           1 |        5 | 1          | 1       | 1           | 3            | 2019-10-24 | 2019-10-25 |
|           1 | Parking 1     |       2 | slot2     |           1 |        2 | 2          | 2       | 1           | 2            | 2019-10-24 | 2019-10-25 |
|           2 | Parking 2     |       3 | slot-p-2  |           2 |       10 | (NULL)     | (NULL)  | (NULL)      | (NULL)       | (NULL)     | (NULL)     |
+-------------+---------------+---------+-----------+-------------+----------+------------+---------+-------------+--------------+------------+------------+

然后，您确定要对哪个部分进行SUM（）。我猜是 capacity和 booking_unit。让我们展示一些选定的列，然后是 GROUP BY l.location_id：

SELECT l.location_id,l.location_name,SUM(capacity) total_capacity,SUM(booking_unit) total_booking 
FROM  location l 
LEFT JOIN slots s ON l.location_id=s.location_id 
LEFT JOIN booking_slots b ON s.location_id=b.location_id AND s.slot_id=b.slot_id
GROUP BY l.location_id;

这将返回以下内容：

+-------------+---------------+----------------+---------------+
| location_id | location_name | total_capacity | total_booking |
+-------------+---------------+----------------+---------------+
|           1 | Parking 1     |              7 | 5             |
|           2 | Parking 2     |             10 | (NULL)        |
+-------------+---------------+----------------+---------------+

对于您的日期过滤器，我们在您的 SUM( CASE WHEN ...语句中使用 SELECT：

SELECT l.location_id,l.location_name,SUM(capacity) AS total_capacity,
      SUM( CASE WHEN b.start_date BETWEEN '2019-10-25' AND '2019-10-28' 
      OR b.end_date BETWEEN '2019-10-25' AND '2019-10-28' THEN booking_unit ELSE 0 END ) AS total_booking 
FROM location l 
LEFT JOIN slots s ON l.location_id=s.location_id 
LEFT JOIN booking_slots b ON s.location_id=b.location_id AND s.slot_id=b.slot_id
GROUP BY l.location_id;

然后您将获得最终结果，如下所示：

+-------------+---------------+----------------+---------------+
| location_id | location_name | total_capacity | total_booking |
+-------------+---------------+----------------+---------------+
|           1 | Parking 1     |              7 | 5             |
|           2 | Parking 2     |             10 | 0             |
+-------------+---------------+----------------+---------------+