检查数据库中是否存在具有范围的数字 [英] Check if a number exists with a range in DB

问题描述

我需要找到一种有效的方法来确定用户提供的数字范围是否已经存在于数据库中的可用范围中.DB中的数字表中有两列:起始数字和结束数字

I need to find an efficient way to find out a if a number range provided by the user is already present in the ranges available in DB. There are two columns in the numbers table in DB which are : Starting Number and Ending Number

假设存在一个范围，其中起始编号为:102，结束编号为 122，那么下一个范围的起始值为:123 或可以是 1 到 101 之间的任何位置

Say a range exists where the Starting Number is : 102 and Ending number is 122 then the Starting of the next range would be : 123 or can be anywhere between 1 to 101

例如，两个范围表列的值如下:

For Example the two Range table columns values are as below :

起始编号 |结束号码

因此，如果用户尝试创建一个新范围，例如:190 到 220 或 85 到 105，他应该会收到错误消息，因为此范围内的数字已经存在在 DB 的范围内.

So if a user tries to create a new range say from : 190 to 220 or 85 to 105 he should get an error since the digits in this range already exists in the ranges in the DB.

因此，根据我上面的示例，应始终允许用户创建超出 1700 的范围，例如 1701 到 1750 或任何内容从缺失的范围说从 251 到 299 或从 1251 到 1299.

So as per my example above, the user should always be allowed to create a range anything beyond 1700 say 1701 to 1750 or anything from missing ranges say from 251 to 299 or from 1251 to 1299.

我能够使用下面的这个查询解决大部分场景，但有一个场景我无法解决:

I was able to solve most of the scenarios using this query below but one scenario i couldnt get to work :

//对于在可用范围之间(不返回值:正常工作)

//For in between ranges which are available (Returns no value : works correctly)

select * from range
where 121  BETWEEN StartingNumber and EndingNumber
or 149  BETWEEN   StartingNumber and EndingNumber;

//对于外部范围(不返回值:正常工作)

//For outer ranges (Returns no value : works correctly)

select * from range
where 1701  BETWEEN StartingNumber and EndingNumber
or 1800  BETWEEN   StartingNumber and EndingNumber;

//对于重叠范围(返回行，因此无法创建此范围:正常工作)

//For overlapping range (Returns rows, so this range cant be created : works correctly)

select * from range
where 150  BETWEEN StartingNumber and EndingNumber
or 160  BETWEEN   StartingNumber and EndingNumber;

//但是对于一个范围，比如 690 到 1800，它失败了，因为它没有返回任何值，但是已经采用了超过 700 到 1700 的范围

select * from range
where 690  BETWEEN StartingNumber and EndingNumber
or 1800  BETWEEN   StartingNumber and EndingNumber;

有没有办法让这最后一个条件也能正常工作?

Is there a way i can get this last condition working as well?

推荐答案

我能够使用下面的这个查询来解决这个问题，它处理了上面提到的所有四个场景.:

I was able to solve this using this query below, it takes care of all the four scenarios mentioned above.:

  SELECT count(StartingNumber) FROM range
  WHERE 690  BETWEEN   StartingNumber and EndingNumber
    or 1800  BETWEEN   StartingNumber and EndingNumber
    or StartingNumber in ( SELECT StartingNumber
                            FROM range
                            WHERE StartingNumber BETWEEN 690 AND 1800);

这篇关于检查数据库中是否存在具有范围的数字的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！