MySQL中的日期字段，查找不重叠的所有行，并仅返回差异 [英] Date fields in MySQL, finding all rows that don't overlap and returning only the difference

问题描述

所以这是我在这里的第一个问题，但我有一个细微的变化：

So this was one of my very first questions here, but I have a slight variation:

所以我有两个人的日程表在一个数据库。时间表只记录两个用户的各种事件/约会的开始时间，结束时间和描述。

So I have two people whose schedules are in a database. The schedules simply record the start time, end time, and description of the various events/appointments for both users.

PersonA想要与PersonB交换约会。我想要一个MySQL查询，它将返回PersonB和PersonA可以交换的所有时间。

PersonA wants to trade appointments with PersonB. I want a MySQL query that will return all of them times that PersonB and PersonA can swap.

最初，查询的参数是抛出PersonB的任何约会与PersonA重叠，PersonB的约会必须与预约PersonA想要交换的长度完全相同。我在时间算术/几何方面得到了一些很好的建议，帮助我得到我需要的结果。

Originally the parameters of the query were to throw out any appointments of PersonB where there was overlap with PersonA and PersonB's appointment had to be the exact same length as the appointment PersonA wants to swap. I got some great advice on time arithmetic/geometry that helped me get the results I needed.

现在我想改变1对1的参数，不必在长度上相等。因此，如果PersonA想要交换他的星期一早上约会（上午10:00 - 11:30），查询将：

Now I want to change the 1-to-1 parameter so that the appointments don't have to be equal in length. So if PersonA wants to swap his Monday morning appointment (10:00 AM - 11:30 AM), the query will:

• 排除任何


• 包括PersonA约会之外的任何PersonB约会


• 包含PersonB约会的部分

因此，如果PersonA想要交换上述约会：00 AM - 11:30 AM），并且PersonA在星期二下午1:00至3:00预约，而PersonB在星期二下午12:00至4:00预约，查询将返回： / p>

So if PersonA wants to swap the above appointment (again, Monday 10:00 AM - 11:30 AM), and PersonA has an appointment on Tuesday 1:00 PM to 3:00 PM and PersonB has an appointment on Tuesday from 12:00 PM to 4:00 PM, the query would return:

Possible_Swaps
==============
userID  | Start             | End             | Description
PersonB | Tuesday, 12:00 PM | Tuesday 1:00 PM | Cooking
PersonB | Tuesday,  4:00 PM | Tuesday 5:00 PM | Cooking

除了任何其他可能性。这是否太期望从数据库？如果是这样，任何建议如何至少获得重叠，但时间挂在任一方，使PHP脚本可以处理这些转变？

In addition to any other possibilities. Is this too much to expect from the database? If so, any suggestions on how to at least get those shifts that are overlapping but have the times hanging over either side so that a PHP script could deal with them?

< hr />

每个searlea的请求，这里有一点上下文：

per searlea's request, here's a bit more context:

我一直说约会，我真的的意思是工作，如工作班次。 PersonA和PersonB在同一个办公室工作。在vcalendar，工作班次通常被称为事件，但有时约会，我与后者，因为它听起来不像两个人去公平。

I kept saying appointments but I think I really meant "jobs" as in "work shifts". PersonA and PersonB work in the same office. In vcalendar, work shifts are usually referred to as "Events" but on occasion "Appointments" and I went with the latter as it sounds less like the two Persons are going to a fair.

所以PersonA星期一从上午10:00到上午11:30洗餐具。 PersonB在星期二下午12:00至下午5:00烹饪。人A真的想看到他的兄弟在他们周一离开城市。

So PersonA has a dish-washing shift on Monday from 10:00 to 11:30 AM. PersonB is cooking on Tuesday from 12:00PM to 5:00PM. PersonA really wants to see his brother before they leave town on Monday. He'd rather get all of Monday morning off, but he'd settle for getting an hour of shift off .

所以在我的老模型（在 href =http://stackoverflow.com/questions/395973/mysql-schedule-conflicts>我的第一个问题在这里），我在寻找任何转变，其中没有重叠，及时。但这有两个问题：

So in my old model (brought up in my very first question here), I was looking for any shifts where there was no overlap and where the shifts were equal in time. But that has two problems:

1. 如果我需要有人来支付我在星期二的2小时轮班，星期四，Joe星期四工作8小时，我可以交换两个小时，他可以提前离开，我可以留一会儿。

1. If I need someone to cover my 2 hour shift on Tuesday and I work for 4 hours on Thursday, and Joe works for 8 hours on Thursday, I could swap two of his hours and he could leave a bit early and I can stay a bit later.

如果我有一个两小时的班次，但我很高兴地交易一个小时，只是为了准时到机场，我想知道是否这样的是在一个小时比我在一个星期稍后的一个小时，所以我

If I have a two hour shift, but I'd gladly trade an hour of it just to make it to the airport on time, I want to know if such and such comes in an hour earlier than me later in the week so I can take that part of his shift.

长短的（太晚），我想要什么是显而易见的PersonA变化的 相对补充 到PersonB（基本上任何时候PersonB都在工作，而PersonA不是，不管偏移在其他点是否重叠。）

Long story short (too late), I want what is apparently known as the relative complement of PersonA's shifts to PersonB (basically any times that PersonB is working and PersonA is not, regardless of whether the shifts overlap at some other point.)

理想情况下，我会得到一组结果，包括PersonB工作的位和PersonA不是（上面提到的两个1小时班次），以及整个班（有一个特殊的标签，表明它不是一个整体），所以PersonA会看到他正在覆盖一部分班次，而不会感到困惑，认为PersonB恰好正在工作两个小时轮班。

Ideally, I would get a set of results that included the bits that PersonB was working and PersonA was not (the two 1 hour shifts mentioned above), as well as the entire shift (with a special tag to indicate it is not available as a whole) so that PersonA would see that he was covering part of a shift and not get confused and think that PersonB just happened to be working two one hour shifts.

这听起来有点复杂。基本上我想PersonB的变化是蓝色的，PersonA的变成黄色，我想让数据库返回所有的不是绿色的部分。

This is all starting to sound a bit complicated. Basically I want PersonB's shifts to be be blue, PersonA's shifts to be yellow, and I want the database to return all of the parts that are not green.

推荐答案

SELECT * 
  FROM schedule AS s1
WHERE
  s1.user = 'Ondra'
AND
NOT EXISTS ( 
  SELECT * FROM schedule AS s2 
  WHERE
    s2.user = 'Zizka'
    AND (
      s2.start BETWEEN s1.start AND s1.end 
      OR
      s2.end BETWEEN s1.start AND s1.end 
      OR 
      s1.start > s2.start AND s1.end < s2.end 
    )
)

这选择了Ondra的事件，可以适应Zizka的日记中的一个缺口。

This selects Ondra's events which can fit into a gap in Zizka's diary.

编辑：最初它是一个交集，但如果你想要相对的补充，这就够了。

Edited: Originally it was an intersect, but if you want the relative complement, this is enough.

这篇关于MySQL中的日期字段，查找不重叠的所有行，并仅返回差异的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！