根据条件选择轮班时间

Question

我有三个表，例如员工、时间槽和调度。例如

Time Slots table : 

 id     time

  1     08:00:00    10:00:00

  2     10:00:00    12:00:00

  3     16:00:00    18:00:00

  4     08:00:00    16:00:00 

  5     14:00:00    18:00:00

Employee Table:

 EMP1

 EMP2

 EMP3

 EMP4

Scheduling table :

 EMP    TIMESLOTS ID 

 EMP1     1

 EMP2     2

 EMP3     4

我需要在 Gridview 的时间段中显示空闲员工的列表，即由于 EMP3 分配在 ( 08:00:00 16:00:00) ，那么它不应该显示在 08:00 之间的任何时间段中:00 16:00:00，但可以在08:00:00之前和16:00:00之后的任何时间段显示，同样适用于所有排定的员工。如果任何员工没有安排在任何时间段，那么该员工应该在每个时间段都有空。即，EMP5 应该在所有时隙中可用。

My output should be like this  :

   EMPLOYEES          FREE TIME SLOTS

    EMP1                   10:00:00    12:00:00

    EMP1                   14:00:00    18:00:00

    EMP1                   16:00:00    18:00:00

    EMP2                   08:00:00    10:00:00

    EMP2                   16:00:00    18:00:00

    EMP2                   14:00:00    18:00:00

    EMP3                   16:00:00    18:00:00

    EMP4                   08:00:00    10:00:00

    EMP4                   10:00:00    12:00:00

    EMP4                   16:00:00    18:00:00

    EMP4                   08:00:00    16:00:00 

    EMP4                   14:00:00    18:00:00

任何帮助都会真正appreaciated。提前致谢

Answer 1

使用 across join生成员工和时间段的所有组合。然后使用 a left join(or not inor not exists) 过滤掉那些存在的：select e.emp, ts.*from employee e cross join     timeslots ts left join     scheduling s     on s.emp = e.emp and s.timeslot_id = ts.timeslot_idwhere s.emp is null;

Answer 2

SELECT e.*, t.*&nbsp;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; FROM employee e&nbsp;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; CROSS JOIN&nbsp;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; TimeSlot t&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; WHERE NOT EXISTs (&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; SELECT 0 FROM Scheduling s2&nbsp;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; JOIN TimeSlot t2&nbsp;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; ON s2.empid = e.empid&nbsp;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; AND t2.endTime > t.StartTime&nbsp;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; AND t2.startTime < t.EndTime&nbsp;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; )&nbsp; --where there is not some other overlapping timeslot allocated