主页 / dba / 问题 / 137512
Accepted
Muflix
Asked: 2016-05-05 06:00:20 +0800 CST

如何检查两个数字之间的范围是否在范围表中可用

  • 772

我有以下数字范围表

Create table Ranges {
  Id int identity(1,1) primary key,
  RangeFrom int not null,
  RangeTo int not null
}

Insert into Ranges(RangeFrom, RangeTo) VALUES (1,4)
Insert into Ranges(RangeFrom, RangeTo) VALUES (5,9)
Insert into Ranges(RangeFrom, RangeTo) VALUES (10,14)
-- here is free space (15 - 19)
Insert into Ranges(RangeFrom, RangeTo) VALUES (20,24)

例如，我想插入一条新记录

Insert into Ranges(RangeFrom, RangeTo) VALUES (15,19) -- Ok
Insert into Ranges(RangeFrom, RangeTo) VALUES (15,30) -- Fail

问题是，在插入之前，我需要检查插入语句中范围之间的数字是否尚未用于表中的任何其他范围。

任何想法 ？

sql-server-2008

3 个回答

  1. select count(*)
from ( select RangeTo, LEAD(RangeFrom, 1, 0) OVER (ORDER BY RangeTo) 
                       AS LeadRangFrom 
       from table 
     ) ss 
where @RangeFrom > RangeTo 
  and ( @RangeTo < LeadRangFrom or LeadRangFrom is null)

    如果 count(*) 是一，那么您可以插入
    这是您要问的问题吗？

    • 3
  2. Best Answer

    您可以使用插入触发器根据表数据检查插入的值，如果范围已存在则失败。

    但是由于您使用的是存储过程，因此您可以签入过程。像这样的东西：

    CREATE PROCEDURE blah (@rangeFrom int, @rangeTo int)
AS
BEGIN
IF EXISTS(
SELECT 1 FROM Ranges 
WHERE @rangeFrom BETWEEN RangeFrom AND RangeTo 
    OR @rangeTo BETWEEN RangeFrom AND RangeTo
    OR RangeTo BETWEEN @rangeTo AND @rangeFrom
    OR RangeFrom BETWEEN @rangeFrom AND @rangeTo
)
return

INSERT INTO Ranges VALUES(@rangeFrom, @rangeTo)


END

    如果您可以确保仅通过存储过程进行插入，这将起作用。如果有人通过 t-sql 插入，只有触发器可以阻止它。

    • 0

  3. 正如其他人所提到的，触发器可以正常工作。您不需要更改存储过程，只需INSTEAD OF INSERT在表上放置一个触发器。

    触发器将查看插入的 RangeFrom 值是否在任何已经在相同范围内的范围之间，然后对 RangeTo 值执行相同的操作。如果它通过该逻辑，它将插入。

    如果失败，它将回滚。请注意，这仅在您像发布的那样进行单独插入时才有效。如果您希望能够插入超过 1 行数据，则需要对此进行调整。

    CREATE TRIGGER tI_Ranges ON Ranges
INSTEAD OF INSERT 
AS
BEGIN 
    IF (SELECT COUNT(*) 
        FROM Ranges t 
        CROSS APPLY inserted 
        WHERE inserted.rangefrom BETWEEN t.rangefrom AND t.rangeto) = 0
          AND (SELECT COUNT(*) 
               FROM Ranges t 
               CROSS APPLY inserted 
               WHERE inserted.rangeto BETWEEN t.rangefrom AND t.rangeto) = 0
    BEGIN 
        INSERT INTO Ranges(rangefrom, rangeto)
            SELECT inserted.rangefrom, inserted.rangeto
            FROM inserted
    END
    ELSE 
    BEGIN 
        ROLLBACK TRAN
    END
END
GO
    • 0

相关问题