查询以检查 sql server 中的重叠范围? [英] Query to check overlapping ranges in 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.

这篇关于查询以检查 sql server 中的重叠范围?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！