查询以找出重叠的小数范围 [英] Query to find out overlapping decimal ranges

问题描述

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

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

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