检查新间隔是否重叠-MySQL(或PHP) [英] Checking if new interval overlaps - MySQL (or PHP)

问题描述

我一直在思考如何简化此处提出的问题. 复杂的MySQL查询-检查重叠的DATE间隔

I've been thinking on how I can simplify the problem presented here. Complex MySQL Query - Checking for overlapping DATE intervals

从本质上讲，用DATES消除所有看中的魔术，这只是检查重叠间隔的问题.毕竟，所有日期都可以视为数字，并且可以使逻辑更容易.想象一下下表:

At it's heart, minus all the fancy magic with DATES this is simply a problem of checking for overlapping intervals. After all dates can be thought of as numbers and it may make the logic easier. Imagine the following table:

Schedules
schedule_id     |     start     |       end 
1               |       1       |       3
2               |       4       |       7                
3               |       8       |       13
4               |      15       |       16
5               |      18       |       24
6               |      25       |       28

我正在尝试插入一个新间隔，以使[a，b]不与任何其他间隔重叠.注意事项:

I'm trying to insert a new interval such that [a,b] do not overlap with any other interval. The considerations:

• 是的，我可以将整个表拉成一个数组并对其进行O(N)搜索.太无聊了.
• 我更喜欢在MySQL中执行此操作，因此不必每次都拉下任意大的表.

请参见下图.这代表了可以插入和不能插入的范围. http://i.stack.imgur.com/jE59w.png

See the following image. This represents the bounds of what can and can not be inserted. http://i.stack.imgur.com/jE59w.png

推荐答案

使用以下缩写:

• [旧] :=现有范围
• [新] :=插入范围
• OS :=(旧)existent_range.start
• OE :=(旧)existent_range.end
• NS :=(新)inserting_range.start
• NE :=(新)inserting_range.end

• [old] := existing range
• [new] := inserting range
• OS := (old) existing_range.start
• OE := (old) existing_range.end
• NS := (new) inserting_range.start
• NE := (new) inserting_range.end

两个范围(旧的和新的)重叠的条件是:(OS < NE) AND (OE > NS)

the condition for overlaping of two ranges (old and new) is: (OS < NE) AND (OE > NS)

虽然解决方案可能并不简单，但到达那里并不是那么困难:

While the solution might be not trivial, its not that difficult to get there:

如果新范围完全在现有范围之前或之后，则没有重叠:[new] <= [old] OR [old] <= [new]，这意味着:

There is no overlaping if the new range is completly before or after the existing range: [new] <= [old] OR [old] <= [new] and that means that:

(NE <= OS) OR (OE <= NS)

通过协商此语句，我们得到了重叠的条件:

Negotiating this statement we get the condition for overlaping:

!( (NE <= OS) OR (OE <= NS) )

现在使用迪摩根定律，我们可以将其写为

Now using De Morgan's law we can write it as

!(NE <= OS) AND !(OE <= NS)

这等效于

(NE > OS) AND (OE > NS)

wich可以重写为

(OS < NE) AND (OE > NS)

现在我们可以使用找到所有重叠的范围

Now we can find all overlaping ranges using

SELECT o.*
FROM Schedules o
WHERE o.start < :new_end
  AND o.end   > :new_start

这篇关于检查新间隔是否重叠-MySQL(或PHP)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！