Разделяет записи на основе диапазона

#sql #sql-server #gaps-and-islands

#sql #sql-сервер #пробелы и острова

Вопрос:

У меня есть 2 таблицы Item и Item Range. В таблице элементов все элементы существуют в диапазоне, т. е. (От и До) номеров. Я должен исключить эти числа из таблицы 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…затем во 2-й строке с 61 по 69, потому что 70-80 в таблице itemrange.. затем от 81 до 500, затем 600-800 так же, как в таблице itemrange, потому что в таблице itemrange не существует строки диапазона. если какая-либо строка найдена в таблице itemrange, в результате она должна быть разделена на 2 записи … пожалуйста, кто-нибудь мне поможет

1. Как исключено? каковы здесь условия?

2. если в таблице @itemRange существует какой-либо диапазон, например, от 50 до 60 и 70-80 .. Пожалуйста, проверьте результат: от 1 до 49 и 61-69, это означает, что от 50 до 60 исключены.. то же самое для 70-80

3. Хорошо, я думаю, вы не понимаете, что я имею в виду, поэтому, пожалуйста, ответьте на это: откуда берутся 49, 61, 69 ..?

4. 49 — это ItemRange (50) минус 1 … и так далее — насколько я понял

5. @EstebanP. да, точно

Ответ №1:

Это проблема островов и пробелов.

Вы можете попробовать использовать два cte рекурсивных, а затем выполнить except .

В заключение используйте Row_number функцию window, чтобы получить номер разрыва, затем сгруппируйте по нему.

Настройка схемы 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 |

1. спасибо @ D-Shih … я предоставил фиктивные данные .. На самом деле разница в черно-белых числах от и до составляет 10 миллионов .. Для меня это не решение .. Можете ли вы, пожалуйста, предоставить мне альтернативные решения..