查询以找出重叠的小数范围

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

我需要找出重叠的小数范围。下面是我的表结构。

I need to find out overlapping decimal ranges. Below is my table structure.

id FromValue ToValue Condition 1 10 15 Between 2 16 NULL Equals 3 6 9 Between 4 17 19 Between 5 16 NULL Greater Than.

当我插入第5行时。我应该无法插入，因为17到19之间已存在范围。当我尝试插入大于16时它不应该允许。 相同条件适用于小于条件。如果我尝试插入少于6.它不应该允许我插入，因为已经存在6到9范围。 请帮我解决SQL查询问题。 已添加代码块 - OriginalGriff [/ edit] 我尝试了什么：

When I insert row 5. I should not be able to insert because there is already a range exists between 17 to 19. When I try to insert Greater than 16 It should not allow. Same condition applies with Less Than condition. If I try to insert less 6. It should not allow me to insert because 6 to 9 range already exists. Please help me out with SQL Query. [edit]Code block added - OriginalGriff[/edit] What I have tried:

declare @fromValue decimal(18,5) declare @toValue decimal(18,5) select * from RangeTest where (fromValue is not null and toValue is not null ) and ( (fromValue >= @fromValue and toValue = @fromValue ))

推荐答案

在你尝试的内容中，你没有考虑使用哪种类型的比较，具体取决于条件列。 我会使用 INSTEAD OF INSERT 触发检查值和条件如下： In what you have tried you don't take into account which type of comparison to use depending on the condition column. I would use an INSTEAD OF INSERT trigger to check value(s) and condition like that: SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TRIGGER [dbo].[Check_Insert] ON [dbo].[RangeTest] INSTEAD OF INSERT AS BEGIN SET NOCOUNT ON; DECLARE @OK bit; DECLARE @FromValue int; DECLARE @ToValue int; DECLARE @Condition nvarchar(20); DECLARE curTest CURSOR LOCAL FAST_FORWARD FOR SELECT [FromValue] , [ToValue] , [Condition] FROM inserted; OPEN curTest; WHILE (1 = 1) BEGIN FETCH NEXT FROM curTest INTO @FromValue, @ToValue, @Condition; IF (@@FETCH_STATUS <> 0) BREAK; SET @OK = 1; IF (@FromValue IS NOT NULL) BEGIN IF EXISTS(SELECT 1 FROM [dbo].[RangeTest] WHERE ([Condition] = 'Equals') AND (@FromValue = [FromValue]) ) BEGIN SET @OK = 0; RAISERROR ('FromValue conflicts with existing Equals entry', 1, 1); END IF EXISTS(SELECT 1 FROM [dbo].[RangeTest] WHERE ([Condition] = 'Between') AND (@FromValue >= [FromValue]) AND (@FromValue <= [ToValue]) ) BEGIN SET @OK = 0; RAISERROR ('FromValue conflicts with existing Between entry', 1, 1); END IF EXISTS(SELECT 1 FROM [dbo].[RangeTest] WHERE ([Condition] = 'Greater Than') AND (@FromValue > [FromValue]) ) BEGIN SET @OK = 0; RAISERROR ('FromValue conflicts with existing Greater-Than entry', 1, 1); END END IF (@ToValue IS NOT NULL) BEGIN IF EXISTS(SELECT 1 FROM [dbo].[RangeTest] WHERE ([Condition] = 'Equals') AND (@ToValue = [FromValue]) ) BEGIN SET @OK = 0; RAISERROR ('ToValue conflicts with existing Equals entry', 1, 1); END IF EXISTS(SELECT 1 FROM [dbo].[RangeTest] WHERE ([Condition] = 'Between') AND (@ToValue >= [FromValue]) AND (@ToValue <= [ToValue]) ) BEGIN SET @OK = 0; RAISERROR ('ToValue conflicts with existing Between entry', 1, 1); END IF EXISTS(SELECT 1 FROM [dbo].[RangeTest] WHERE ([Condition] = 'Greater Than') AND (@ToValue > [FromValue]) ) BEGIN SET @OK = 0; RAISERROR ('ToValue conflicts with existing Greater-Than entry', 1, 1); END END IF (@OK = 1) INSERT INTO [dbo].[RangeTest] ([FromValue] ,[ToValue] ,[Condition]) VALUES (@FromValue ,@ToValue ,@Condition) END CLOSE curTest; DEALLOCATE curTest; END GO