我在 mysql 中有一个这样的表:
+------------+------------+------------+------------+
| date | user_id | start_hour | end_hour |
+------------+------------+------------+------------+
| 2010-12-15 | 20 | 08:00:00 | 08:15:00 |
| 2010-12-15 | 20 | 14:00:00 | 14:30:00 |
| 2010-12-15 | 20 | 17:00:00 | 17:45:00 |
+------------+------------+------------+------------+
我尝试提取用户时间的时间范围我在这里找到并举例,但我无法在几个小时内完成这项工作
我尝试了查询:
$sql="
SELECT a.end_hour AS 'Available From', Min(b.start_hour) AS 'To'
FROM (
SELECT 0 as date, '08:00:00' as start_hour,'08:00:00' as end_hour
UNION SELECT date, start_hour, end_hour FROM table
)
AS a JOIN
( SELECT date, start_hour, end_hour FROM table
UNION SELECT 0, '21:00:00' as start_hour, '22:00:00' as end_hour
) AS b ON
a.date=b.date AND a.user_id=b.user_id AND a.end_hour < b.start_hour WHERE
a.date='$date' AND a.user_id='$user_id' GROUP BY a.end_hour
HAVING a.end_hour < Min(b.start_hour);";
我需要创建一个从 08:00 到 21:00 的范围,约会之间的空闲块喜欢这个:
free time
08:15:00 to 14:00:00
14:30:00 to 17:00:00
17:45:00 to 21:00:00
尝试此查询
SELECT
a.id,
a.start_hour,
a.end_hour,
TIMEDIFF(la.start_hour, a.end_hour) as `Free Time`
FROM appointment as a
LEFT JOIN(SELECT * FROM appointment LIMIT 1,18446744073709551615) AS la
ON la.id = a.id + 1
LEFT JOIN (SELECT * FROM appointment) AS ra ON a.id = ra.id
这将显示这些结果
+---------------------------------------------+
¦ id ¦ start_hour BY ¦ end_hour | Free Time |
¦----+---------------¦------------------------|
¦ 1 ¦ 08:00:00 ¦ 08:15:00 | 05:45:00 |
¦ 2 ¦ 14:00:00 ¦ 14:30:00 | 02:30:00 |
¦ 3 ¦ 17:00:00 ¦ 17:45:00 | 03:15:00 |
¦ 4 ¦ 21:00:00 ¦ 21:00:00 | (NULL) |
+--------------------+------------------------+
此外,表中必须有 21:00:00,否则您将无法获得最后的时差。 我在表中输入了 21:00:00 作为开始和结束日期。
编辑
这是修改后的查询
SELECT
a.id,
a.end_hour AS `Free time Start`,
IFNULL(la.start_hour,a.end_hour) AS `Free Time End`,
IFNULL(TIMEDIFF(la.start_hour, a.end_hour),'00:00:00') AS `Total Free Time`
FROM appointment AS a
LEFT JOIN (SELECT * FROM appointment LIMIT 1,18446744073709551615) AS la
ON la.id = (SELECT MIN(id) FROM appointment where id > a.id LIMIT 1)
结果是
+--------------------------------------------------------+
¦ id ¦ Free time Start ¦ Free Time End | Total Free Time |
¦----+-----------------¦---------------------------------|
¦ 1 ¦ 08:15:00 ¦ 14:00:00 | 05:45:00 |
¦ 2 ¦ 14:30:00 ¦ 17:00:00 | 02:30:00 |
¦ 3 ¦ 17:45:00 ¦ 21:00:00 | 03:15:00 |
¦ 4 ¦ 21:00:00 ¦ 21:00:00 | 00:00:00 |
+----------------------+---------------------------------+
从此查询中学习的要点是
- 时间差异函数用法。 时间差异("结束时间","开始时间")
- 使用大数连接
- 避免连接中的第一条记录具有长偏移量,并限制从 1 而不是零开始
- IFNULL 用法 ifnull('如果这里来空','选择这个然后')
试试这个查询,我希望它对你有用.....
SELECT
a.id,
a.end_hour AS `Free time Start`,
la.start_hour AS `Free Time End`,
IFNULL(TIMEDIFF(la.start_hour, a.end_hour),'00:00:00') AS `Total Free Time`
FROM appointment AS a
LEFT JOIN (SELECT *
FROM appointment
LIMIT 1,18446744073709551615) AS la
ON la.id = (SELECT
id
FROM appointment
WHERE id NOT IN(a.id)
ORDER BY (a.id > id)ASC
LIMIT 1);
这将为您提供结束时间块
SELECT T_a.end_hour
FROM (table AS T_a) LEFT JOIN (table AS T_b)
ON (T_a.`date`=T_b.`date` AND T_b.start_hour<=T_a.end_hour AND T_b.end_hour>T_a.end_hour)
WHERE T_b.user_id IS NULL
这将为您提供开始时间块
SELECT T_a.start_hour
FROM (table AS T_a) LEFT JOIN (table AS T_b)
ON (T_a.`date`=T_b.`date` AND T_b.start_hour<T_a.start_hour AND T_b.end_hour>=T_a.start_hour)
WHERE T_b.user_id IS NULL
这些查询将查找未交叉或对接到其他插槽的开始或结束。所以 10-11 11-12 将给出 10 开始,12 结束 = 空闲时间是 08-10 和 12-21
所以空闲时间是 08:00(除非是开始时间)到第一个"开始"时间,然后匹配的 END->START 到最后一个 END->21:00(除非 21:00 是最后一个结束)
注:注:您还需要添加日期。
也许这已经结束了,但这适用于sql-server,不确定转换为mysql的努力
declare @AvailableTime table ( AvailableDate date, StartTime time, EndTime time )
insert into @AvailableTime values
('15 dec 2010', '08:00:00', '21:00:00')
declare @Booking table (BookingDate date, UserId int, StartTime Time, EndTime Time)
insert into @Booking values
('15 dec 2010', 20, '08:00:00', '08:15:00'),
('15 dec 2010', 20, '14:00:00', '14:30:00'),
('15 dec 2010', 20, '17:00:00', '17:45:00')
select
*
from
(
-- first SELECT get start boundry
select t.StartTime s, b.StartTime e
from @AvailableTime t, @Booking b
where
t.AvailableDate = b.BookingDate and (t.StartTime <= b.EndTime and b.StartTime <= t.EndTime) and t.StartTime <> b.StartTime
and
not exists (select 1 from @Booking b2 where b2.BookingDate = b.BookingDate and b2.StartTime < b.StartTime)
union
-- second SELECT get spikes ie middle
select b1.EndTime s, b2.StartTime e
from @AvailableTime t, @Booking b1, @Booking b2
where
t.AvailableDate = b1.BookingDate and (t.StartTime <= b1.EndTime and b1.StartTime <= t.EndTime)
and
t.AvailableDate = b2.BookingDate and (t.StartTime <= b2.EndTime and b2.StartTime <= t.EndTime)
and
b1.EndTime < b2.StartTime
and
not exists (select 1 from @Booking b3 where b3.BookingDate = t.AvailableDate and b3.StartTime > b1.EndTime and b3.EndTime < b2.StartTime)
union
-- third SELECT get end boundry
select b.EndTime s, t.EndTime e
from @AvailableTime t, @Booking b
where
t.AvailableDate = b.BookingDate and (t.StartTime <= b.EndTime and b.StartTime <= t.EndTime) and t.EndTime <> b.EndTime
and
not exists (select 1 from @Booking b2 where b2.BookingDate = b.BookingDate and b2.StartTime > b.StartTime)
) t1