我有一个卡车停车应用程序，在该应用程序中，卡车运输公司可以预订特定日期的某个位置的停车位数量。
我需要帮助来修改查询以查找特定时期的停车位。
我在此过程中有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中有任何选项，这样我可以获取某个位置的组合插槽容量？
最佳答案
让我们尝试一下：
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 |
+-------------+---------------+----------------+---------------+