查询以检查 sql server 中的重叠范围?

本文介绍了查询以检查 sql server 中的重叠范围?的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

我的桌子是这样的

From_Range ToRange 1 999 9000 10000 2000 5000

当我尝试插入范围值 1000 - 3000 时，它应该会失败，因为此新范围内的某些值介于现有范围 2000 - 5000 之间.如何检查输入范围是否在现有范围内?

When I try to insert the range values 1000 - 3000 it should fail since some values within this new range fall between the existing range 2000 - 5000. How do I check whether the input range falls within the existing range?

推荐答案

找到重叠的最简单方法是这样的:

The easiest way to find an overlap is like this:

IF EXISTS (SELECT 1 FROM table WHERE @myValueLo <= ExistingRangeEnd AND @myValueHi >= ExistingRangeStart) -- Overlaps ELSE -- Doesn't overlap

如果您将上面的条件与下图中的每个条形进行比较，就可以证明这是有效的:

This can be shown to work if you compare the condition above against each of the bars in the diagram below:

Existing range: |-------------------| Overlaps: |-------------| |------------| |----------------------------------| |-------------| Not overlaps: |-----| |----|

在所有重叠情况下，这两个测试都是正确的:

in all the overlap cases, both these tests are true:

• 现有范围的开始日期总是在新范围的结束日期之前
• 现有范围的结束日期在新范围的开始日期之后

• the start date of the existing range is always before the end date of the new range
• the end date of the existing range is after the start date of the new range

那些不重叠的测试不及格.

Those that don't overlap fail one or other of this test.