sql - 员工排类系统

Question

我有员工表，我试图只查询时间戳范围内可用的员工。

select 
    emp_fn_name, emp_fn_name,
    TO_TIMESTAMP(e.start_time, 'HH12:MIAM,PM')::TIME,
    TO_TIMESTAMP(e.end_time, 'HH12:MIAM,PM')::TIME
from 
    employees e
inner join 
    schedule s on emp_id = e.id
where 
    TO_TIMESTAMP(s.start_time, 'HH12:MIAM,PM')::TIME
        not BETWEEN TO_TIMESTAMP('2:00PM', 'HH12:MIAM,PM')::TIME
                AND TO_TIMESTAMP('03:00PM', 'HH12:MIAM,PM')::TIME

时间表的示例数据:

start   end     days    emp_id  emp_fn
2:00    3:00PM  TuTh    38      e1
2:00    3:00PM  TuTh    154     e2
4:00    5:00AM  TuTh    154     e3
6:00    7:00AM    MW    154     e1
7:00    8:00AM    MW    154     e4

如果我要找人在下午 2:00 到 3:00 为 TuTh 工作，我应该找 emp3 和 emp4，因为他们在指定的时间范围内不忙。

谢谢。

Answer 1

我想你想要:

select emp_fn_name, emp_fn_name
from employees e 
where not exists (select 1
                  from schedule s
                  where s.emp_id = e.id and
                        s.start < '03:00PM'::time and
                        s.end > '02:00PM'::time
                 );