代码之家 › 专栏 › 技术社区 › Anthony

MySQL计划冲突

schedule calendar datetime mysql

Anthony · 技术社区 · 16 年前

嘿,我偶然发现这个网站在mysql表中寻找事件重叠的解决方案。我对这个解决方案印象深刻(这已经有帮助了),我想我会看看是否能得到更多的帮助…

好吧,乔想和工作中的人换班。他有出庭日期。他去了换班表,这张表拉高了本周的时间表(或者剩下的时间)。这是通过数据库查询完成的。没有汗水。他选了一个班次。从这一点来看,它变得棘手。

因此,首先,表单将shift start和shift end传递给脚本。它为任何具有与此班次重叠的班次的人运行查询。它们不能同时工作两个班次,因此查询中的所有用户ID都被列入黑名单。此查询如下:

SELECT DISTINCT user_id FROM shifts
WHERE
FROM_UNIXTIME('$swap_shift_start') < shiftend
AND FROM_UNIXTIME('$swap_shift_end') > shiftstart

接下来,我们对所有轮班运行一个查询,其中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

现在,您可能想知道“什么是$冲突日期?”??”

好吧,当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”,然后我发现使用了中间值,必须从两端减去一分钟。)

谢谢!

一

2 回复 | 直到 16 年前

Adam Bellaire 16 年前

我认为您应该能够使用内部选择而不是生成的字符串排除Joe的其他轮班,比如:

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

基本上,每一行都有一个关于乔的移位计数的内部查询,这些移位重叠,并确保它为零。因此,只返回与Joe现有的任何班次都不重叠的行。

Bill Karwin 16 年前

你可以加载 joe_shift{1,2,3} 将值放入临时表中,然后对其进行查询以进行联接,使用外部联接只查找不匹配任何值的移位:

CREATE TEMPORARY TABLE joes_shifts (
 shiftstart DATETIME
 shiftend   DATETIME
);
INSERT INTO joes_shifts (shiftstart, shiftend) VALUES
  ('$joe_shift1_start', '$joe_shift1_end'),
  ('$joe_shift2_start', '$joe_shift2_end'),
  ('$joe_shift3_start', '$joe_shift3_end'),
  ('$joe_shift4_start', '$joe_shift4_end');
-- make sure you have validated these variables to prevent SQL injection

SELECT s.*
FROM shifts s
  LEFT OUTER JOIN joes_shifts j
  ON (j.shiftstart < s.shiftend OR j.shiftend > s.shiftstart) 
WHERE j.shiftstart IS NULL
  AND s.shiftstart BETWEEN FROM_UNIXTIME('$startday') AND FROM_UNIXTIME('$endday')
  AND s.user_id NOT IN ('blacklisteduser1', 'blacklisteduser2',...etc) 
  AND (TIME_TO_SEC(TIMEDIFF(s.shiftend,s.shiftstart)) = '$swap_shift_length');

由于左外部联接,当中没有匹配行时 joes_shifts ,列为空。