人员的sql日期-时间比较

Question

我想列出一个没有厨师的人的工作清单。

sql表：

people_id clock_in               clock_out
1         2016-10-10 06:58:01.000 2016-10-10 14:59:01.000 
1         2016-10-11 06:57:02.000 2016-10-11 14:58:23.000 
..
2         2016-10-10 07:51:01.000 2016-10-10 13:00:01.000 

在本例中，People_id: 1=普通工人，2=厨师

现在我想要显示1在2不在的情况下工作的时间

如下所示：

people_id start_working_without_chef end_working_without_chef
1         2016-10-10 06:58:01.000    2016-10-10 07:51:01.000
1         2016-10-10 13:00:01.000    2016-10-10 14:59:01.000 

在这个例子中，只有一个工人和一个厨师，但这必须扩展到几个工人和几个首领。

如何使用sql查询来解决此问题？

Answer 1

嗯，这看起来不是很“好”，我明天会试着找到一个更好的解决方案。

create table tt (id int, dte_in timestamp, dte_out timestamp);

insert into tt values (1, timestamp('2016-10-10-06.58.01'), timestamp('2016-10-10-14.59.01'));
insert into tt values (1, timestamp('2016-10-11 06:57:02'), timestamp('2016-10-11 14:58:23'));
insert into tt values (1, timestamp('2016-10-12 06:57:02'), timestamp('2016-10-12 14:58:23'));
insert into tt values (1, timestamp('2016-10-13 06:57:02'), timestamp('2016-10-13 14:58:23'));
insert into tt values (1, timestamp('2016-10-14 06:57:02'), timestamp('2016-10-14 14:58:23'));
insert into tt values (2, timestamp('2016-10-10 07:51:01'), timestamp('2016-10-10 13:00:01'));
insert into tt values (2, timestamp('2016-10-12 05:57:02'), timestamp('2016-10-12 15:58:23'));
insert into tt values (2, timestamp('2016-10-13 05:57:02'), timestamp('2016-10-13 12:58:23'));
insert into tt values (2, timestamp('2016-10-14 10:57:02'), timestamp('2016-10-14 16:58:23'));


select a.id, 
 a.dte_in,
 b.dte_in
from tt a
inner join tt b on b.id = 2 and b.dte_in between a.dte_in and a.dte_out 
where a.id = 1
union all
select a.id, 
 b.dte_out,
 a.dte_out
from tt a
inner join tt b on b.id = 2 and b.dte_out between a.dte_in and a.dte_out 
where a.id = 1
union all
select a.id, a.dte_in, a.dte_out
from tt a
where a.id = 1 and not exists (select 1 from tt b where b.id = 2 and b.dte_in between a.dte_in and a.dte_out) 
and not exists (select 1 from tt b where b.id = 2 and a.dte_in between b.dte_in and b.dte_out)