SQL Server 2017

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

DECLARE @Item TABLE
(
	[Id] INT,
	[ItemId] INT, 
	[FROM] INT,
	[TO] INT
)

DECLARE @ItemRange TABLE
(
	[Id] INT,
	[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)

--合併Item重疊部分
--排除B區間 A[ B[] ]
--並合併這種類型的區間
-- A[   ]
-- B    [   ]
;WITH CTE_MERGE AS
(
	SELECT A.ItemId, A.[FROM], A.[TO],
	       CAST(A.Id AS NVARCHAR(MAX)) AS Id,
	       CAST(NULL AS NVARCHAR(MAX)) AS PId1, 
		   CAST(NULL AS NVARCHAR(MAX)) AS PId2
	FROM @Item AS A
	LEFT JOIN @Item AS B
		ON B.ItemId=A.ItemId AND
		   --排除A被包在B的資料，這種資料會造成下方無窮迴圈
		   B.Id<>A.Id AND
		   B.[FROM] <= A.[FROM] AND B.[TO] >= A.[TO]
	WHERE B.Id IS NULL
	UNION ALL
	SELECT A.ItemId, A.[FROM],
		   CASE WHEN A.[TO] > B.[TO] THEN A.[TO] ELSE B.[TO] END AS [TO],
		   --重新編一個Id
		   A.Id+'-1' AS Id,
		   A.Id AS PId1,
		   CAST(B.Id AS NVARCHAR(MAX)) AS PId2
	FROM CTE_MERGE AS A
	CROSS APPLY (
		SELECT *
		FROM (
			SELECT *, ROW_NUMBER() OVER (ORDER BY B.Id) AS R FROM @Item AS B
			WHERE B.ItemId=A.ItemId AND
			      --找到可以合併的區間
			      CAST(B.Id AS NVARCHAR(MAX))<>A.Id AND
				  A.[TO]+1 >= B.[FROM] AND A.[TO]+1 <= B.[TO]
		) AS X
		WHERE X.R=1
	) AS B
)

--SELECT * FROM CTE_MERGE

--排除遞迴中遺留的部分
,CTE_MERGE_CLEAR AS 
(
	SELECT A.Id, A.ItemId, A.[FROM], A.[TO]
	FROM CTE_MERGE AS A
	LEFT JOIN CTE_MERGE AS B ON A.Id=B.PId1 OR A.Id=B.PId2
	--沒有父Id的資料才是最後結果
	WHERE B.Id IS NULL
)

--SELECT * FROM CTE_MERGE_CLEAR

--排除ItemRange部分
--判斷重疊有這五種可能
-- A [   ]  [ ]    [   ] [   ]   [   ]
-- B  [ ]  [   ] [   ]     [   ] [   ]
,CTE AS
(
	SELECT ItemId, [FROM], [TO], Id,
		   CAST(NULL AS NVARCHAR(MAX)) AS PId
	FROM CTE_MERGE_CLEAR
	UNION ALL
	SELECT *
	FROM (
		SELECT A.ItemId,
		       CASE WHEN G=1 THEN A.[FROM] ELSE B.[TO]+1 END AS [FROM],
		       CASE WHEN G=1 THEN B.[FROM]-1 ELSE A.[TO] END AS [TO],
			   --重新編一個Id
		       CASE WHEN G=1 THEN A.Id+'-1' ELSE A.Id+'-2' END AS Id,
			   A.Id AS PId
		FROM CTE AS A
		CROSS APPLY (
			SELECT *
			FROM (
				SELECT *, ROW_NUMBER() OVER (ORDER BY B.Id) AS R FROM @ItemRange AS B
				WHERE B.ItemId=A.ItemId AND
				      --找到Item和ItemRange重疊的部分
					  ((A.[FROM] >= B.[FROM] AND A.[FROM] <= B.[TO]) OR
					   (A.[TO] >= B.[FROM] AND A.[TO] <= B.[TO]) OR
					   (A.[FROM] <= B.[FROM] AND A.[TO] >= B.[TO]))
			) AS X
			--取一筆就好，一次迴圈拆一次區間就好
			WHERE X.R=1
		) AS B
		--將一筆資料拆成兩筆
		CROSS APPLY (VALUES(1),(2)) AS C(G)
	) AS X
	--負區間就不要了
	WHERE X.[TO]-X.[FROM] > 0
)

--SELECT * FROM CTE

--排除遞迴中遺留的部分
,CTE_CLEAR AS 
(
	SELECT A.ItemId, A.[FROM], A.[TO]
	FROM CTE AS A
	LEFT JOIN CTE AS B ON A.Id=B.PId
	--沒有父Id的資料才是最後結果
	WHERE B.Id IS NULL
)

SELECT * 
FROM CTE_CLEAR
ORDER BY ItemId, [FROM]

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