[SQL]连续範围排除另一个範围问题

在 S.O上有一个SQL问题Splits records based on range

在大家分享我的解法

有两张表

Item Table

| Id | ItemId | FROM |  To ||----|--------|------|-----||  1 |      1 |    1 | 100 ||  2 |      1 |  101 | 500 ||  3 |      1 |  600 | 700 |

ItemRange Table

| Id | ItemId | FROM | To ||----|--------|------|----||  1 |      1 |   50 | 60 ||  2 |      1 |   70 | 80 |

期望输出

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

SQLfiddle

问题描述：

提问者想要产生一个结果集，关于Item表依照 ItemId 创立 [FROM] 到 [TO] 的值，但要排除在
ItemRange表连续範围内的值。

例如：
Item表有一列 1 ~ 100

| ItemId | FROM  |  To || 1      |    1  | 100 |

但由于ItemRange表有两列 50 ~ 60和70 ~ 80

| ItemId | FROM  |  To || 1      |   50  | 60  || 1      |   70  | 80  |

所以这部份期望输出，Item表要排除ItemRange表连续範围数字

如下

| ItemId | FROM |  TO ||--------|------|-----||      1 |    1 |  49 ||      1 |   61 |  69 ||      1 |   81 | xxx |

我看到连续範围，就会联想这个是一个(Islands and gaps problem)

我的想法是要先两个full连续範围资料表，我先使用CTE递迴来实作

之后再使用 except 对于 Item 排除 ItemRange 要排除的数字範围

;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)

最后就是重头戏，使用ROW_NUMBER + Window Function 做出标示连续範围的群组

SELECT ItemId,       MIN([FROM]) 'FROM',       MAX([FROM]) 'TO'FROM (  SELECT ItemId,[FROM],[FROM] - ROW_NUMBER() OVER(ORDER BY [FROM]) grp  FROM CTE3) t1GROUP BY grp,ItemIdoption (maxrecursion 0)

解答SQLfiddle

目前提问者还有一个问题是他的範围太大，导致执行效能不是很好 这个问题我目前还没有特别的解法 欢迎有想法的大大可以提供建议

P.S. 我觉得此问题很清楚，但我不知道为什么会被投Close