問題描述
我在 mysql 中有一個(gè)這樣的表:
I have a table in mysql like this:
+------------+------------+------------+------------+
| 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 |
+------------+------------+------------+------------+
我嘗試提取用戶時(shí)間的時(shí)間范圍我在此處找到并舉例，但我無法在幾個(gè)小時(shí)內(nèi)完成這項(xiàng)工作
and I try to extract the time range of the time of users I found and example here, but I can't make that work on hours
我試過查詢:
$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);";
我需要?jiǎng)?chuàng)建一個(gè)從 08:00 到 21:00 的范圍，其中包含約會(huì)之間的空閑塊像這樣:
I need to create a range since 08:00 to 21:00 with the free blocks between the appointments like this:
free time
08:15:00 to 14:00:00
14:30:00 to 17:00:00
17:45:00 to 21:00:00
推薦答案
試試這個(gè)查詢
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
這將顯示這些結(jié)果
+---------------------------------------------+
| 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，否則您將無法獲得上次時(shí)差.我在表中輸入了 21:00:00 作為開始和結(jié)束日期.
Also you must have the 21:00:00 in the table or you wont be able to get the last time difference. i entered 21:00:00 as start and end date in the table.
這是修改后的查詢
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)
結(jié)果是
+--------------------------------------------------------+
| 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 |
+----------------------+---------------------------------+
從這個(gè)查詢中學(xué)習(xí)的要點(diǎn)是
The points to learn from this query are
- Timediff 函數(shù)的使用.timediff('結(jié)束時(shí)間','開始時(shí)間')
- 用大號(hào)加入
- 避免使用長(zhǎng)偏移量連接的第一條記錄，并限制從 1 而不是零開始
- IFNULL 用法 ifnull('if here come null','select this then')
這篇關(guān)于在 mysql 和 php 中尋找空閑的時(shí)間塊?的文章就介紹到這了，希望我們推薦的答案對(duì)大家有所幫助，也希望大家多多支持html5模板網(wǎng)！