尝试这个查询
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 作为开始和结束日期。
EDITED
这是修改后的查询
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 |
+----------------------+---------------------------------+
从这个查询中可以学到的要点是
- Timediff函数的使用。 timediff('结束时间','开始时间')
- 与上位数字连接
- 避免连接中的第一条记录具有长偏移量并限制从 1 而不是从零开始
- IFNULL 用法 ifnull('如果此处为 null','则选择此')