按照范围拆分记录

Question

我有两个表，一个是物品(Item)表，另一个是物品范围(Item Range)表。在物品(Item)表中，所有的物品都存在于范围内，即(从和到)数字。我需要从物品范围(Item Range)表中排除那些数字，即(50到60和70到80)。

Declare @Item table
(
    Id int primary key, 
    ItemId int,
    [FROM] int,
    [To] int
)

Declare @ItemRange table
(
    Id int primary key, 
    ItemId int,
    [FROM] int,
    [To] int
)

INSERT INTO @Item 
VALUES  
(1,1,1,100),
(2,1,101,500),
(3,1,600,700)

INSERT INTO @ItemRange 
VALUES  
(1,1,50,60),
(2,1,70,80)

预期结果：

第一行显示1到49，因为50至60在表格的项目范围内...然后第二行显示61到69，因为70至80在表格的项目范围内...然后81到500以及600-800与表格的项目范围相同，因为在项目范围内没有范围行存在...如果结果中有任何一行在表格的项目范围内，则应将其拆分为两个记录...请帮我解决此问题。

- Ajt

---

排除在哪里？这里有什么条件？ - Ilyes

好的，我想你不明白我的意思，请回答这个问题：49、61、69是从哪里来的？ - Ilyes

49是ItemRange(50)减1...等等，就我所理解的。 - Esteban P.

@EstebanP。好的，这解释了61是怎么来的，但69不是。 - Ilyes

@Sami，因为存在项目范围表70到80..所以69出现了..同时下一行从81开始。 - Ajt

Answer 1

这是一个关于岛屿和间隙问题的解决方案。

您可以尝试使用两个递归的cte，然后执行except操作。

最后使用Row_number窗口函数获取间隙编号，然后按此分组。 MS SQL Server 2017模式设置:

CREATE  table Item
(
    Id int primary key, 
    ItemId int,
    [FROM] int,
    [To] int
)

CREATE table ItemRange
(
    Id int primary key, 
    ItemId int,
    [FROM] int,
    [To] int
)

INSERT INTO Item 
VALUES  
(1,1,1,100),
(2,1,101,500),
(3,1,600,700)

INSERT INTO ItemRange 
VALUES  
(1,1,50,60),
(2,1,70,80)

Query 1:

;WITH CTE AS (
  SELECT ItemId,[FROM],[TO]
  FROM Item
  UNION ALL
  SELECT ItemId,[FROM]+ 1,[TO]
  FROM CTE
  WHERE [FROM]+ 1 <= [TO]
), CTE2 AS(
  SELECT ItemId,[FROM],[TO]
  FROM ItemRange
  UNION ALL
  SELECT  ItemId,[FROM]+ 1,[TO]
  FROM CTE2
  WHERE [FROM]+ 1 <= [TO]
),CTE3 AS(
  SELECT ItemId,[FROM]
  FROM CTE
  except
  SELECT ItemId,[FROM]
  FROM CTE2
)
SELECT ItemId,
       MIN([FROM]) 'FROM',
       MAX([FROM]) 'TO'
FROM (
  SELECT ItemId,[FROM],[FROM] - ROW_NUMBER() OVER(ORDER BY [FROM]) grp
  FROM CTE3
) t1
GROUP BY grp,ItemId
option (maxrecursion 0)

Results:

| ItemId | FROM |  TO |
|--------|------|-----|
|      1 |    1 |  49 |
|      1 |   61 |  69 |
|      1 |   81 | 500 |
|      1 |  600 | 700 |

- D-Shih

---

谢谢@D-Shih...我已经提供了虚拟数据..实际上从和到数字之间的差异是1000万..CTE对我来说不是解决方案..你能否提供替代方案？ - Ajt