我的数据库中有两个表有关系。我使用MySQL。基本上,我创建了一个应用程序来管理"五人制足球的现场订单"所以,我们来了:
第一张表名为Lapangan,意思是"印度尼西亚语中的田野":
mysql> SELECT id,nama_lapangan FROM lapangan;
+----+---------------+
| id | nama_lapangan |
+----+---------------+
| 1 | Lap 01 |
| 2 | Lap 02 |
| 3 | Lap 03 |
+----+---------------+
3 rows in set (0.00 sec)
第二张表是预订: ,
mysql> SELECT id, nomor_booking, date_booking, date_end_booking, lapangan_id FROM `yfutsal`.`booking` LIMIT 1000;
+----+---------------+---------------------+---------------------+-------------+
| id | nomor_booking | date_booking | date_end_booking | lapangan_id |
+----+---------------+---------------------+---------------------+-------------+
| 1 | 1 | 2017-07-16 10:00:00 | 2017-07-16 12:00:00 | 1 |
| 2 | 2 | 2017-07-16 15:00:00 | 2017-07-16 16:00:00 | 3 |
+----+---------------+---------------------+---------------------+-------------+
这就是问题所在。
例如,所有者从 08.00 开始,到 23.00 结束。
这意味着,
第 1 圈不适用于 10.00- - 12.00
- 第 3 圈在 15.00 - 16.00 不可用。
目标是,我想显示小时字段可用的 Lapangan(字段(,基于小时不包括上述案例的小时。
因此,收银员可以选择它。像这样:
+----+---------------+----------------------+-----------------------+
| id | nama_lapangan | Available Start | Available End |
+----+---------------+----------------------+-----------------------+
| 1 | Lap 01 | 2017-07-16 08:00:00 | 2017-07-16 09:59:00 |
| 1 | Lap 01 | 2017-07-16 12:01:00 | 2017-07-16 23:00:00 |
| 2 | Lap 02 | 2017-07-16 08:00:00 | 2017-07-16 23:00:00 |
| 3 | Lap 03 | 2017-07-16 08:00:00 | 2017-07-16 14:59:00 |
| 3 | Lap 03 | 2017-07-16 16:01:00 | 2017-07-16 23:00:00 |
+----+---------------+----------------------+-----------------------+
请从心底里指教。
这是一个相当棘手的目标,没有窗口函数(MySQL 5.7没有(。这是可行的,即使它不容易理解。
为了方便起见,我用了一个额外的表,我称之为parameters
:
CREATE TABLE parameters
(
start_date_time DATETIME,
end_date_time DATETIME
) ;
INSERT INTO parameters
VALUES ('2017-07-16 08:00', '2017-07-16 23:00') ;
这个想法是,对于给定的lapangan(字段(,如果有一天只有一个预订(start_booking_1,end_booking_1(,我们将得到两个可用时段:
start_date_time .. start_booking_1 <- first period
end_booking_1 .. end_date_time <- 2
如果有一天有两个预订(start_booking_1、end_booking_1(和(start_booking_2、end_booking_2(排序,我们将有:
start_date_time .. start_booking_1 <- first period
end_booking_1 .. start_booking_2 <- 2
end_booking_2 .. end_date_time <- 3
因此,我们需要将第一个周期与其他周期区分开来:
第一个自由段可以通过以下方式计算:
-- first free period
SELECT
lapangan.id, parameters.start_date_time AS available_start,
coalesce( (SELECT date_start_booking
FROM booking b
WHERE b.lapangan_id = lapangan.id
ORDER BY b.date_start_booking ASC
LIMIT 1
), parameters.end_date_time) AS available_end
FROM
lapangan, parameters
注意:搜索start_booking_1
的方法是糟糕的子查询。如果它没有返回值,我们将转到end_date_time
。
中间(和最后一个(周期的计算公式为:
-- 2..n free periods
SELECT
lapangan.id,
b1.date_end_booking AS available_start,
coalesce ( (SELECT date_start_booking
FROM booking b2
WHERE b2.lapangan_id = b1.lapangan_id
AND b2.date_start_booking >= b1.date_end_booking
ORDER BY b2.date_start_booking ASC
LIMIT 1),
(SELECT parameters.end_date_time
FROM parameters)
) AS avilable_end
FROM
lapangan
JOIN booking b1 ON b1.lapangan_id = lapangan.id
你必须把所有东西放在一起,并照顾可能的空期。然后你会得到
SELECT DISTINCT
lapangan.id, lapangan.nama_lapangan, av.available_start, av.available_end AS available_end
FROM
(
-- first free period
SELECT
lapangan.id, parameters.start_date_time AS available_start,
coalesce( (SELECT date_start_booking
FROM booking b
WHERE b.lapangan_id = lapangan.id
ORDER BY b.date_start_booking ASC
LIMIT 1
), parameters.end_date_time) AS available_end
FROM
lapangan, parameters
UNION
-- 2..n free periods
SELECT
lapangan.id,
b1.date_end_booking AS available_start,
coalesce ( (SELECT date_start_booking
FROM booking b2
WHERE b2.lapangan_id = b1.lapangan_id
AND b2.date_start_booking >= b1.date_end_booking
ORDER BY b2.date_start_booking ASC
LIMIT 1),
(SELECT parameters.end_date_time
FROM parameters)
) AS avilable_end
FROM
lapangan
JOIN booking b1 ON b1.lapangan_id = lapangan.id
) AS av
JOIN lapangan ON lapangan.id = av.id
WHERE
-- Ignore empty segments
av.available_start < av.available_end
ORDER BY
lapangan.id, available_start ;
这将为您提供预期的结果。
ID | nama_lapangan | available_start | available_end -: |:------------ |:------------------ |:------------------ 1 |第 01 圈 |2017-07-16 08:00:00 |2017-07-16 10:00:00 1 |第 01 圈 |2017-07-16 12:00:00 |2017-07-16 23:00:00 2 |第 02 圈 |2017-07-16 08:00:00 |2017-07-16 23:00:00 3 |第 03 圈 |2017-07-16 08:00:00 |2017-07-16 15:00:00 3 |第 03 圈 |2017-07-16 16:00:00 |2017-07-16 23:00:00
请注意,我没有从开始或结束中减去 1 分或 1 秒。假设您的经期被考虑在内:
start <= available_time < end
或者,在范围内术语
[start, end)
如果您确实需要减去 1 秒,请在选择的第一行进行。
您可以在dbfiddle上检查所有内容(使用一些分步方法来获得解决方案( 这里
旁注:如果您使用知道如何使用LEAD
或LAG
窗口函数的数据库,这将容易得多。
这个问题的本质是在没有任何存储的地方生成数据行。换句话说,您存储了已预订的小时数,但没有未预订的小时数。所以你需要一种方法来生成这些。虽然有几种替代方案,但一个简单的选择是使用笛卡尔乘积来做到这一点。
在这里,我选择将操作时间存储在表中,作为一组整数 8 到 23(根据您的意图,可能是 8 到 22(。然后,可以在所有字段 (nama_lapangan( 的所有小时的任何给定日期的交叉连接(以生成笛卡尔乘积(中使用此功能。生成后,我们可以将所有可用时间保留到已经预订的这些时间,然后在当前没有相关预订(IS NULL(的情况下,我们确定当天的可用时间。
数据
CREATE TABLE OpenHours
(`StartAt` int)
;
INSERT INTO OpenHours (`StartAt`)
VALUES
(8), (9), (10), (11), (12), (13), (14), (15),
(16), (17), (18), (19), (20), (21), (22), (23)
;
CREATE TABLE lapangan
(`id` int, `nama_lapangan` varchar(6))
;
INSERT INTO lapangan (`id`, `nama_lapangan`)
VALUES
(1, 'Lap 01'), (2, 'Lap 02'), (3, 'Lap 03')
;
CREATE TABLE yfutsal
(`id` int, `nomor_booking` int, `date_booking` datetime, `date_end_booking` datetime, `lapangan_id` int)
;
INSERT INTO yfutsal
(`id`, `nomor_booking`, `date_booking`, `date_end_booking`, `lapangan_id`)
VALUES
(1, 1, '2017-07-16 10:00:00', '2017-07-16 12:00:00', 1),
(2, 2, '2017-07-16 15:00:00', '2017-07-16 16:00:00', 3)
;
查询
set @dt := str_to_date('2017-07-16','%Y-%m-%d');
select
l.id
, l.nama_lapangan
, date_add(@dt,INTERVAL h.StartAt HOUR) AvailStartHr
, date_add(@dt,INTERVAL h.StartAt+1 HOUR) AvailEndHr
from lapangan l
cross join OpenHours h
left join yfutsal y on l.id = y.lapangan_id
and date_add(@dt,INTERVAL h.StartAt HOUR) between date_booking and date_end_booking
where y.date_booking IS NULL
order by l.nama_lapangan, AvailStartHr
;
结果
| id | nama_lapangan | AvailStartHr | AvailEndHr |
|----|---------------|---------------------|---------------------|
| 1 | Lap 01 | 2017-07-16 08:00:00 | 2017-07-16 09:00:00 |
| 1 | Lap 01 | 2017-07-16 09:00:00 | 2017-07-16 10:00:00 |
| 1 | Lap 01 | 2017-07-16 13:00:00 | 2017-07-16 14:00:00 |
| 1 | Lap 01 | 2017-07-16 14:00:00 | 2017-07-16 15:00:00 |
| 1 | Lap 01 | 2017-07-16 15:00:00 | 2017-07-16 16:00:00 |
| 1 | Lap 01 | 2017-07-16 16:00:00 | 2017-07-16 17:00:00 |
| 1 | Lap 01 | 2017-07-16 17:00:00 | 2017-07-16 18:00:00 |
| 1 | Lap 01 | 2017-07-16 18:00:00 | 2017-07-16 19:00:00 |
| 1 | Lap 01 | 2017-07-16 19:00:00 | 2017-07-16 20:00:00 |
| 1 | Lap 01 | 2017-07-16 20:00:00 | 2017-07-16 21:00:00 |
| 1 | Lap 01 | 2017-07-16 21:00:00 | 2017-07-16 22:00:00 |
| 1 | Lap 01 | 2017-07-16 22:00:00 | 2017-07-16 23:00:00 |
| 1 | Lap 01 | 2017-07-16 23:00:00 | 2017-07-17 00:00:00 |
| 2 | Lap 02 | 2017-07-16 08:00:00 | 2017-07-16 09:00:00 |
| 2 | Lap 02 | 2017-07-16 09:00:00 | 2017-07-16 10:00:00 |
| 2 | Lap 02 | 2017-07-16 10:00:00 | 2017-07-16 11:00:00 |
| 2 | Lap 02 | 2017-07-16 11:00:00 | 2017-07-16 12:00:00 |
| 2 | Lap 02 | 2017-07-16 12:00:00 | 2017-07-16 13:00:00 |
| 2 | Lap 02 | 2017-07-16 13:00:00 | 2017-07-16 14:00:00 |
| 2 | Lap 02 | 2017-07-16 14:00:00 | 2017-07-16 15:00:00 |
| 2 | Lap 02 | 2017-07-16 15:00:00 | 2017-07-16 16:00:00 |
| 2 | Lap 02 | 2017-07-16 16:00:00 | 2017-07-16 17:00:00 |
| 2 | Lap 02 | 2017-07-16 17:00:00 | 2017-07-16 18:00:00 |
| 2 | Lap 02 | 2017-07-16 18:00:00 | 2017-07-16 19:00:00 |
| 2 | Lap 02 | 2017-07-16 19:00:00 | 2017-07-16 20:00:00 |
| 2 | Lap 02 | 2017-07-16 20:00:00 | 2017-07-16 21:00:00 |
| 2 | Lap 02 | 2017-07-16 21:00:00 | 2017-07-16 22:00:00 |
| 2 | Lap 02 | 2017-07-16 22:00:00 | 2017-07-16 23:00:00 |
| 2 | Lap 02 | 2017-07-16 23:00:00 | 2017-07-17 00:00:00 |
| 3 | Lap 03 | 2017-07-16 08:00:00 | 2017-07-16 09:00:00 |
| 3 | Lap 03 | 2017-07-16 09:00:00 | 2017-07-16 10:00:00 |
| 3 | Lap 03 | 2017-07-16 10:00:00 | 2017-07-16 11:00:00 |
| 3 | Lap 03 | 2017-07-16 11:00:00 | 2017-07-16 12:00:00 |
| 3 | Lap 03 | 2017-07-16 12:00:00 | 2017-07-16 13:00:00 |
| 3 | Lap 03 | 2017-07-16 13:00:00 | 2017-07-16 14:00:00 |
| 3 | Lap 03 | 2017-07-16 14:00:00 | 2017-07-16 15:00:00 |
| 3 | Lap 03 | 2017-07-16 17:00:00 | 2017-07-16 18:00:00 |
| 3 | Lap 03 | 2017-07-16 18:00:00 | 2017-07-16 19:00:00 |
| 3 | Lap 03 | 2017-07-16 19:00:00 | 2017-07-16 20:00:00 |
| 3 | Lap 03 | 2017-07-16 20:00:00 | 2017-07-16 21:00:00 |
| 3 | Lap 03 | 2017-07-16 21:00:00 | 2017-07-16 22:00:00 |
| 3 | Lap 03 | 2017-07-16 22:00:00 | 2017-07-16 23:00:00 |
| 3 | Lap 03 | 2017-07-16 23:00:00 | 2017-07-17 00:00:00 |
参见:http://sqlfiddle.com/#!9/775f36/4
在前一个答案中,您将学习如何逐行生成所需的可用小时数。获得该数据后,可以使用以下MySQL技术将其汇总为范围:
set @dt := str_to_date('2017-07-16','%Y-%m-%d')
SELECT id, nama_lapangan, AvailStart, MAX(AvailEndHr) AvailEndHr
FROM (
SELECT
mytable.*
, @fin := IF(@lid<=>id AND TIMESTAMPDIFF(HOUR, @d, AvailStartHr)=1, @fin, AvailStartHr) AS AvailStart
, @lid := id
, @d := AvailStartHr
FROM (
select
l.id
, l.nama_lapangan
, date_add(@dt,INTERVAL h.StartAt HOUR) AvailStartHr
, date_add(@dt,INTERVAL h.StartAt+1 HOUR) AvailEndHr
from lapangan l
cross join OpenHours h
left join yfutsal y on l.id = y.lapangan_id
and date_add(@dt,INTERVAL h.StartAt HOUR) between date_booking and date_end_booking
where y.date_booking IS NULL
) mytable
CROSS JOIN (SELECT @lid:=NULL, @d:=NULL, @fin:= NOW()) AS init
ORDER BY id, AvailStartHr
) d
GROUP BY id, nama_lapangan, AvailStart
结果见sqlfiddle:
| id | nama_lapangan | AvailStart | AvailEndHr |
|----|---------------|---------------------|---------------------|
| 1 | Lap 01 | 2017-07-16 08:00:00 | 2017-07-16 10:00:00 |
| 1 | Lap 01 | 2017-07-16 13:00:00 | 2017-07-17 00:00:00 |
| 2 | Lap 02 | 2017-07-16 08:00:00 | 2017-07-17 00:00:00 |
| 3 | Lap 03 | 2017-07-16 08:00:00 | 2017-07-16 15:00:00 |
| 3 | Lap 03 | 2017-07-16 17:00:00 | 2017-07-17 00:00:00 |
另请参阅之前关于类似主题的答案。