需要帮助这个MySQL查询。查找在特定时间可用的用户 [英] Need help with this MySQL query. Finding users who are available at a certain time

问题描述

这是我的数据库的简化模式：

Here is a simplified schema of my database:

Users
------------------------------------
UserID | Name      | Assignable
------------------------------------
  5    | John Doe  |      1

TimeSlots
-------------------------------------------------------
TimeSlotID | StartTime           | EndTime
-------------------------------------------------------
     3     | 2011-06-30 15:00:00 | 2011-06-30 16:00:00

Appointments
------------------------------------
TimeSlotID | UserID
------------------------------------
     3     |    5   

我有可以分配给组成约会的时间段的用户。我需要一种从开始和结束时间开始的方法，并查询所有能够被分配的用户（可分配标志设置为1），并且没有与那些时间的时间冲突（预约重叠）。

I have Users that can be assigned to TimeSlots that make up Appointments. I need a way to start with a start and end time and query all Users that are able to be assigned (Assignable flag set to 1) and have no time conflicts (Appointments that overlap) with those times.

旧的TimeSlots也会在那里，所以你只会对现在或将来的TimeSlots感兴趣。

Older TimeSlots will be in there too so you would only be interested in TimeSlots that are now or in the future.

推荐答案

select *
from users u    
where u.Assignable = 1
    and u.UserID not in (
       select UserID
       from Appointments a
         join TimeSlots t on a.TimeSlotID = t.TimeSlotID 
       where t.EndTime > now()
          and t.EndTime > @desiredStartTime
          and t.StartTime < @desiredEndTime
    )

---

编辑从tandu中取一个线索

---

edit Taking a cue from tandu

我认为这也可以工作， ：

I think this would also work, and it has the added performance benefit of no subqueries:

select *
from users u    
    left join Appointments a on a.UserID = u.UserID
    left join TimeSlots t on (
    a.TimeSlotID = t.TimeSlotID 
       and t.EndTime > now()
       and t.EndTime > @desiredStartTime
       and t.StartTime < @desiredEndTime
    )
where 
    u.Assignable = 1
    and t.TimeSlotID is null

这篇关于需要帮助这个MySQL查询。查找在特定时间可用的用户的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！