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

数据库中是否存在数字范围

sql-server-2005

0

sventevit · 技术社区 · 16 年前

在我的表格中,我有八个文本框,如下所示(用户在第一个文本框中写“25”,在第二个文本框中写“35”):

Code1From: _25____   Code1To:_35____
Code2From: _______   Code2To:_______
Code3From: _______   Code3To:_______
Code4From: _______   Code4To:_______

我的桌子看起来像这样:

create table MyTable (
    Id int identity(1,1),
    Code1From bigint, Code1To bigint,
    Code2From bigint, Code2To bigint,
    Code3From bigint, Code3To bigint,
    Code4From bigint, Code4To bigint
)

现在我想阻止插入数据,即allready inserted。例如: MyTable中的数据:

Id, Code1From, Code1To, Code2From, Code2To, Code3From, Code3To, Code4From, Code4To
1, 1, 10, null, null, null, null, null, null
2, 11, 20, null, null, null, null, null, null
3, 21, 30, null, null, null, null, null, null
4, 31, 40, null, null, null, null, null, null
5, 41, 50, null, null, null, null, null, null

如果用户希望将代码1(或代码2、代码3或代码4)的值从25插入到35,我应该提出一个错误然而,例如,用户可以插入51到55的范围。

如何确定我的数据库中的跨度是否已准备就绪?现在我可以这样做:

select *
from MyTable
where
   @code1From between Code1From and Code1To
or @code1From between Code2From and Code2To
or @code1From between Code3From and Code3To
or @code1From between Code4From and Code4To
--
or @code1To between Code1From and Code1To
or @code1To between Code2From and Code2To
or @code1To between Code3From and Code3To
or @code1To between Code4From and Code4To
--
... and another 24 or statements

有没有更简单的方法来实现这一点?

1 回复 | 直到 10 年前

1

Adriaan Stander 16 年前

DECLARE @Table TABLE(
        FromVal1 FLOAT,
        ToVal1 FLOAT,
        FromVal2 FLOAT,
        ToVal2 FLOAT,
        FromVal3 FLOAT,
        ToVal3 FLOAT,
        FromVal4 FLOAT,
        ToVal4 FLOAT
)

INSERT INTO @Table (FromVal1,ToVal1,FromVal2,ToVal2) SELECT 1, 10, 51, 60
INSERT INTO @Table (FromVal2,ToVal2) SELECT 11, 20
INSERT INTO @Table (FromVal3,ToVal3) SELECT 21, 30
INSERT INTO @Table (FromVal4,ToVal4) SELECT 31, 40
INSERT INTO @Table (FromVal1,ToVal1) SELECT 41, 50

DECLARE @FromVal FLOAT,
        @ToVal FLOAT

SELECT  @FromVal = 25,
        @ToVal = 35

SELECT  *
FROM    @Table
WHERE   NOT(FromVal1 > @ToVal OR ToVal1 < @FromVal)
OR      NOT(FromVal2 > @ToVal OR ToVal2 < @FromVal)
OR      NOT(FromVal3 > @ToVal OR ToVal3 < @FromVal)
OR      NOT(FromVal4 > @ToVal OR ToVal4 < @FromVal)