MySQL中的日期字段，查找不重叠的所有行并仅返回差异 [英] Date fields in MySQL, finding all rows that don't overlap and returning only the difference
问题描述
所以我有两个人的日程安排在一个数据库中。这些日程表只记录了两个用户的各种事件/约会的开始时间，结束时间和描述。
PersonA想要与PersonB交易约会。我想要一个MySQL查询，它将返回所有这些PersonB和PersonA可以互换的次数。
最初查询的参数是抛出任何PersonB的约会，在那里与PersonA重叠，PersonB的任命必须与PersonA要交换的约定完全相同。我有一些很好的建议，时间算术/几何，帮助我得到我需要的结果。
现在我要更改1对1参数，以便约会不要等长。所以如果PersonA想换一个星期一早上约会（10:00 AM到11:30 AM），查询将会：
- 排除任何在人事任命之一中的PersonB的约定
- 包含PersonA约会之外的任何PersonB的约会
- 包含PersonB的约会的部分当PersonA是免费的，但只显示空闲部分。
所以如果PersonA想要交换上述约会（再次，星期一10 ：00 AM - 11:30 AM），PersonA在星期二下午1:00至3:00预约，PersonB在星期二12:00至4:00预约，查询将返回：
Possible_Swaps
==============
userID |开始|结束|描述
PersonB |星期二，12:00 PM |星期二下午1点烹饪
PersonB |周二，下午4:00 |星期二下午5点烹饪
除了任何其他可能性。这是不是期望从数据库？如果是这样，任何关于如何至少获得重叠但有时间悬挂在两边以便PHP脚本可以处理这些变化的建议？
每个搜索者的要求，这里有一个更多的上下文：
我一直在说约会，但我认为我的意思是工作 工作排班。 PersonA和PersonB在同一个办公室工作。在vcalendar中，工作班通常被称为活动，但有时候预约，我和后者一起去，因为听起来不像两个人要公平。
所以PersonA在星期一上午10:00至11:30进行洗碗班。 PersonB在星期二下午12:00至5:00进行烹饪。 PersonA在星期一离开城市之前真的很想见他的哥哥。他宁愿在星期一早上休息一下，但他会安定一个小时的班次。
所以在我的旧模特里href =http://stackoverflow.com/questions/395973/mysql-schedule-conflicts>我的第一个问题在这里），我正在寻找任何没有重叠的轮班，哪些班次是相等的及时。但是这有两个问题：
-
如果我需要有人在星期二覆盖我的2小时班，我工作4个小时星期四，乔星期四工作了8个小时，我可以换掉他的两个小时，他可以早点离开，我可以留一会儿。
-
如果我有两个小时的班次，但是我会很乐意交易一小时，只是为了及时到达机场，我想知道这样的事情是否比本周晚些时候比我早一个小时，所以我可以承担他的转变的那一部分。
长篇小说（太迟），我想要显然被称为PersonA转入PersonB的 相对补充 基本上任何时候，PersonB都在工作，PersonA不是，无论移动在某个其他位置是否重叠。）
理想情况下，我会得到一组结果，包括的位PersonB正在工作，PersonA不是（上述两个1小时班次），以及整个班次（有一个特殊的标签，表示它不能作为一个整体），所以PersonA会看到他被覆盖了一个转变，不要困惑，认为PersonB刚刚正好工作两个小时班。
这一切都开始听起来有点复杂。基本上我希望PersonB的变化是蓝色的，PersonA的变化是黄色的，我希望数据库返回所有不是绿色的部分。
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
）
）
这将选择适合Zizka日记中的差距的Ondra的事件。
编辑：本来是一个相交，但如果你想要相对补充，这就够了。
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 wants to trade appointments with PersonB. I want a MySQL query that will return all of them times that PersonB and PersonA can swap.
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.
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:
- Exclude any of PersonB's appointments that are during one of PersonA's appointments
- Include any of PersonB's appointments that are outside of PersonA's appointments
- Include the parts of PersonB's appointments that are while PersonA is free, but only show the free portion.
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
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?
per searlea's request, here's a bit more context:
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.
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 .
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:
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.
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.)
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.
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
)
)
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屋！
