如何在数据列表中验证该范围不重叠？

Question

我有一份数据清单：

Id   StartAge     EndAge      Amount 
1           0        2        50
2           2        5        100
3           5        10       150    
4           6        9        160

我必须为不同年龄组设定金额。

The age group  >0 and <=2 need to pay 50
The age group  >2 and <=5 need to pay 100
The age group  >5 and <=10 need to pay 150

但是The age group >6 and <=9需要支付160是一个无效的输入，因为>6 and <=9已经存在于150个金额范围内。

在插入数据之前，我必须验证这种无效的输入，因为bulk.Once 5-10范围将被插入，任何在此范围内的内容都不应该被系统接受。例如:在上面的列表中，应该允许用户插入10-15岁的年龄组，但是以下任何一个都应该被检查为无效。

1. 6-9
2. 6-11
3. 3-5
4. 5-7

如果我的列表中存在无效的输入，我不需要插入该列表。

Answer 1

您可以尝试先将数据插入临时表。

DECLARE @TempData TABLE
(
    [Id] TINYINT
   ,[StartAge] TINYINT
   ,[EndAge] TINYINT
   ,[Amount] TINYINT 
);    
INSERT INTO @TempData ([Id], [StartAge], [EndAge], [Amount])
VALUES (1, 0, 2, 50)
      ,(2, 2, 5, 100)
      ,(3, 5, 10, 150)
      ,(4, 6, 9, 160);

然后，使用INSERT将这些数据传输到目标表.选择.语句

INSERT INTO <your target table>
SELECT * FROM  @TempData s
WHERE 
NOT EXISTS (
    SELECT 1
    FROM @TempData t
    WHERE 
      t.[Id] < s.[Id] 
      AND s.[StartAge] < t.[EndAge]
      AND s.[EndAge] > t.[StartAge] 
    );

我创建了一个演示这里

Answer 2

我们可以使用递归CTE来查找end age和start age对如何链接记录：

DECLARE @DataSource TABLE
(
    [Id] TINYINT
   ,[StartAge] TINYINT
   ,[EndAge] TINYINT
   ,[Amount] TINYINT 
);

INSERT INTO @DataSource ([Id], [StartAge], [EndAge], [Amount])
VALUES (1, 0, 2, 50)
      ,(2, 2, 5, 100)
      ,(3, 5, 10, 150)
      ,(4, 6, 9, 160)
      ,(5, 6, 11, 160)
      ,(6, 3, 5, 160)
      ,(7, 5, 7, 160)
      ,(9, 10, 15, 20)
      ,(8, 7, 15, 20);


WITH PreDataSource AS (
    SELECT *, ROW_NUMBER() OVER (PARTITION BY [StartAge] ORDER BY [id]) as [pos]
    FROM @DataSource
), DataSource AS
(
    SELECT [Id], [StartAge], [EndAge], [Amount], [pos]
    FROM PreDataSource
    WHERE [id] = 1
    UNION ALL
    SELECT R.[Id], R.[StartAge], R.[EndAge], R.[Amount], R.[pos]
    FROM DataSource A
    INNER JOIN PreDataSource R
        ON A.[Id] < R.[Id]
        AND A.[EndAge] = R.[StartAge]
        AND R.[pos] =1

)
SELECT [Id], [StartAge], [EndAge], [Amount]
FROM DataSource;

这给了我们以下输出：

​

​

注意，在此之前，我们使用以下语句准备数据：

SELECT *, ROW_NUMBER() OVER (PARTITION BY [StartAge] ORDER BY [id]) as [pos]
FROM @DataSource;

这样做的目的是找到具有相同起始年龄的记录，并计算先插入哪个记录。那么，在CTE中，我们只得到了第一个。

Answer 3

假设要将上述数据大容量插入到临时表(#tmp)或表变量(@tmp)中。

如果您正在使用server 2012，请尝试以下操作。

select * 
from(select *,lag(endage,1,0)over(order by endage) as [col1]
from @tmp)tmp
where startage>=col1 and endage>col1

此查询的结果应插入到主表中。