MySQL计划冲突 [英] MySQL schedule conflicts

问题描述

嘿，我偶然发现这个网站正在寻找mySQL表中事件重叠的解决方案。我对这个解决方案（这是帮助已经）印象深刻我想我会看到，如果我可以得到一些更多的帮助...

好吧，所以乔想要与工作中的人交换。他有法庭日期。他去了换班交换形式，它提高了本周的时间表（或剩下的时间）。这是通过DB查询完成的。没有汗水。他选择了一班。从这一点，它得到多刺。

所以，首先，形式通过shift开始和shift end到脚本。它对任何具有与此移位重叠的移位的人运行查询。他们不能一次工作两班，所以来自此查询的所有用户ID都放在黑名单上。此查询如下所示：

  SELECT DISTINCT user_id FROM shifts 
 WHERE 
 FROM_UNIXTIME（'$ swap_shift_start'） < shiftend 
 AND FROM_UNIXTIME（'$ swap_shift_end'）>接下来，我们对所有轮班进行查询，即a）长度相同（公司策略）。 ，并且b）不与Joe正在工作的任何其他移位重叠。
 
 
 
我当前具有的是这样的：
  SELECT * 
 FROM shifts 
 AND shiftstart BETWEEN FROM_UNIXTIME（'$ startday'）AND FROM_UNIXTIME（'$ endday'）
 AND user_id NOT IN（$ busy_users）
 AND（TIME_TO_SEC（TIMEDIFF（shiftend，shiftstart））='$ swap_shift_length'）
 $ conflict_dates 
 ORDER BY shiftstart，lastname 
  
现在，你可能想知道what is $ conflict_dates ???
 
 
 
当Joe提交掉换班时，如果他决定检查另一个班次的潜力，他会重新加载班次。所以当它做第一次查询，当脚本循环并输出他的选择，它也建立一个看起来像一样的字符串：
  AND NOT（
'joe_shift1_start'< shiftend 
 AND'joe_shift1_end'> shiftstart）
 AND NOT（
'joe_shift2_start'< shiftend 
 AND'joe_shift2_end'> shiftstart）
 ... etc 
  
正在得到一个很长的查询语句：
  SELECT * 
 FROM shifts 
 AND shiftstart BETWEEN FROM_UNIXTIME（'$ startday'）AND FROM_UNIXTIME（'$ endday'）
 AND user_id NOT IN（'blacklisteduser1'，'blacklisteduser2'，... etc）
 AND（TIME_TO_SEC（TIMEDIFF（shiftend，shiftstart ））='$ swap_shift_length'）
 AND NOT（
'joe_shift1_start'< shiftend 
 AND'joe_shift1_end'> shiftstart）
 AND NOT（
'joe_shift2_start '< shiftend 
 AND'joe_shift2_end'> shiftstart）
 AND NOT（
'joe_shift3_start'< shiftend 
 AND'joe_shift3_end'> shiftstart）
 AND NOT（
'joe_shift4_start'< shiftend 
 AND'joe_shift4_end'> shiftstart）
 ... etc 
 ORDER BY shiftstart，lastname 
  
所以，我的希望是，SQL有一些天才的方式来处理这个更简单的方式，有人可以指出一个梦幻般的逻辑原理，以一种更聪明的方式解决潜在的冲突。 （注意使用'start> end，end< start'，之前我发现我使用了betweens，并且必须从两端减去一分钟。）
 
 
 
谢谢！
 
 
 
 A 
解决方案
使用内部选择而不是生成的字符串排除Joe的其他轮班，例如：
  SELECT * 
 FROM班s1 
 AND shiftstart BETWEEN FROM_UNIXTIME（'$ startday'）AND FROM_UNIXTIME（'$ endday'）
 AND user_id NOT IN（$ busy_users）
 AND（TIME_TO_SEC（TIMEDIFF（moveend，shiftstart）） ='$ swap_shift_length'）
 AND（SELECT COUNT（1）FROM shifts s2 
 WHERE s2.user_id = $ joes_user_id 
 AND s1.shiftstart< s2.shiftend 
 AND s2 .shiftstart< s1.shiftend）= 0 
 ORDER BY shiftstart，lastname 
  
每行都有一个内部查询，用于计算Joe的重叠的移动，并确保它的值为零。因此，只有不与Joe的任何现有轮班重叠的行将被返回。
 
Hey, I stumbled upon this site looking for solutions for event overlaps in mySQL tables. I was SO impressed with the solution (which is helping already) I thought I'd see if I could get some more help...

Okay, so Joe want's to swap shifts with someone at work. He has a court date. He goes to the shift swap form and it pull up this week's schedule (or what's left of it). This is done with a DB query. No sweat. He picks a shift. From this point, it gets prickly.

So, first, the form passes the shift start and shift end to the script. It runs a query for anyone who has a shift that overlaps this shift. They can't work two shifts at once, so all user IDs from this query are put on a black list. This query looks like:
SELECT DISTINCT user_id FROM shifts
WHERE
FROM_UNIXTIME('$swap_shift_start') < shiftend
AND FROM_UNIXTIME('$swap_shift_end') > shiftstart
Next, we run a query for all shifts that are a) the same length (company policy), and b) don't overlap with any other shifts Joe is working.

What I currently have is something like this:
SELECT *
FROM shifts
AND shiftstart BETWEEN  FROM_UNIXTIME('$startday') AND FROM_UNIXTIME('$endday')
AND user_id NOT IN ($busy_users) 
AND (TIME_TO_SEC(TIMEDIFF(shiftend,shiftstart)) = '$swap_shift_length')
$conflict_dates
ORDER BY shiftstart, lastname
Now, you are probably wondering "what is $conflict_dates???"

Well, when Joe submits the swap shift, it reloads his shifts for the week in case he decides to check out another shift's potential. So when it does that first query, while the script is looping through and outputting his choices, it is also building a string that looks kind of like:
AND NOT(
'joe_shift1_start' < shiftend
AND 'joe_shift1_end' > shiftstart)
AND NOT(
'joe_shift2_start' < shiftend
AND 'joe_shift2_end' > shiftstart)
...etc
So that the database is getting a pretty long query along the lines of:
SELECT *
FROM shifts
AND shiftstart BETWEEN  FROM_UNIXTIME('$startday') AND FROM_UNIXTIME('$endday')
AND user_id NOT IN ('blacklisteduser1', 'blacklisteduser2',...etc) 
AND (TIME_TO_SEC(TIMEDIFF(shiftend,shiftstart)) = '$swap_shift_length')
AND NOT(
'joe_shift1_start' < shiftend
AND 'joe_shift1_end' > shiftstart)
AND NOT(
'joe_shift2_start' < shiftend
AND 'joe_shift2_end' > shiftstart)
AND NOT(
'joe_shift3_start' < shiftend
AND 'joe_shift3_end' > shiftstart)
AND NOT(
'joe_shift4_start' < shiftend
AND 'joe_shift4_end' > shiftstart)
...etc
ORDER BY shiftstart, lastname
So, my hope is that either SQL has some genius way of dealing with this in a simpler way, or that someone can point out a fantastic logical principal that accounts for the potential conflicts in a much smarter way. (Notice the use of the 'start > end, end < start', before I found that I was using betweens and had to subtract a minute off both ends.)

Thanks!

A
解决方案
I think you should be able to exclude Joe's other shifts using an inner select instead of the generated string, something like:
SELECT *
FROM shifts s1
AND shiftstart BETWEEN  FROM_UNIXTIME('$startday') AND FROM_UNIXTIME('$endday')
AND user_id NOT IN ($busy_users) 
AND (TIME_TO_SEC(TIMEDIFF(shiftend,shiftstart)) = '$swap_shift_length')
AND (SELECT COUNT(1) FROM shifts s2
     WHERE s2.user_id = $joes_user_id
     AND   s1.shiftstart < s2.shiftend
     AND   s2.shiftstart < s1.shiftend) = 0
ORDER BY shiftstart, lastname
Basically, each row has an inner query for the count of Joe's shifts which overlap, and makes sure that it's zero.  Thus, only rows which don't overlap with any of Joe's existing shifts will be returned.

                        
这篇关于MySQL计划冲突的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！