时间戳

首页 > 解决方案 > 根据范围拆分记录

sql - 根据范围拆分记录

问题描述

我有 2 个表项目和项目范围。在项目表中，所有项目都存在于范围内，即（从和到）数字。我必须从 ItemRange 表中排除这些数字，即（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 在表 itemrange 中......然后在第二行 61 到 69 因为表 itemrange 中的 70-80 .. 然后 81 到 500 然后 600-800 与表 itemrange 相同，因为表itemrange中不存在范围行..如果在结果中的表itemrange中找到任何行，它应该拆分为2条记录...请任何人帮助我

标签： sqlsql-servergaps-and-islands

解决方案

这是一个孤岛和差距问题。

您可以尝试使用两个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)

查询 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)

结果：

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

推荐阅读