MySQL 查询从约会中选择空闲时段

Question

我尝试从我的预约系统中选择免费时间。表1:

table1: working hours

worker   starthour  endhour
---------------------------
1        10:00      18:00

table2: appointments

app   starthour  endhour
---------------------------
1        10:30      11:00
2        12:00      12:40

所以，我需要显示这样的内容:

available hours

starthour  endhour
------------------
10:00      10:30
11:00      12:00
12:40      18:00

如有任何帮助，我们将不胜感激!

这是我的代码，但我不确定这是最好的主意:

select x.eh, min(y.sh)

from 
(
select '18:00' as sh, '10:00' as eh from dual
union
select '10:30' as sh, '11:00' as eh from dual union
select '12:00' as sh, '13:00' as eh from dual
) x,
(
select '18:00' as sh, '10:00' as eh from dual
union
select '10:30' as sh, '11:00' as eh from dual union
select '12:00' as sh, '13:00' as eh from dual
) y
where x.eh <> x.sh
and     y.sh > x.eh
group by x.eh
order by x.eh

Answer 1

解决了!这是一个答案:

select freestart, freeend from (
select 
    max(x.eh) as freestart, y.sh as freeend   
from (
select '18:00' as sh, '10:00' as eh from dual
union
select '10:00' as sh, '10:30' as eh from dual
union
select '12:00' as sh, '12:40' as eh from dual
union
select '15:00' as sh, '15:20' as eh from dual
) x,
(
select '18:00' as sh, '10:00' as eh from dual
union
select '10:00' as sh, '10:30' as eh from dual
union
select '12:00' as sh, '12:40' as eh from dual
union
select '15:00' as sh, '15:20' as eh from dual
) y
where x.eh <> y.sh
and     y.sh > x.eh
and x.eh <> y.eh
group by y.sh
order by x.eh
) z
order by z.freestart