mysql空闲时间查询_mysql – 我有一个SQL查询,用于查找特定日期后最近的空闲时间.如何在此日期之前查找日期？...

一个简单的解决方案是使用date_sub和order by语句,将结果限制为仅显示1条记录.

这是结果：

SELECT date_sub(start_time, interval duration second) as free_before FROM `notes` where start_time>'2015-10-21 16:00:00' order by start_time asc limit 1

为你加分

使用以前的解决方案@Richard提供.将它们放在一起以显示1个表中的所有空闲时间可能会导致以下结果：

select * from (SELECT date_sub(start_time, interval duration second) as free_times FROM `notes` where start_time>'2015-10-21 16:00:00' order by start_time asc limit 1) a

union

(SELECT (a.start_time + INTERVAL a.duration SECOND) AS free_times FROM notes a

WHERE

NOT EXISTS ( SELECT 1 FROM notes b WHERE b.start_time

BETWEEN (a.start_time + INTERVAL a.duration SECOND) AND

(a.start_time + INTERVAL a.duration SECOND) + INTERVAL 15 SECOND - INTERVAL 1 MICROSECOND) AND

(a.start_time + INTERVAL a.duration SECOND) BETWEEN '2015-10-21 19:41:30' AND '2015-10-21 19:43:50')

编辑：

我只会写我的部分查询.另一部分工作正常,只有你想让我改变它我会(永远不会修复没有破坏的东西)

如果你想要10秒的间隔 – >

SELECT date_sub(start_time, interval 10 second) as free_times FROM `notes` where start_time>'2015-10-21 16:00:00' order by start_time asc limit 1

如果你想间隔15秒 – >

SELECT date_sub(start_time, interval 15 second) as free_times FROM `notes` where start_time>'2015-10-21 16:00:00' order by start_time asc limit 1

在这种情况下,您必须相应地更改start_time和持续时间.