我假设“Start”是包含在内的,“End”是排除在外的,并且给定的间隔不重叠。
“CTE_Number”是一个数字表。这里它是即时生成的。我已经将其作为永久表储存在我的数据库中。
“CTE_T1”和“CTE_T2”使用数字表将每个间隔扩展到相应的行数。例如,区间[2,5)会生成具有“Values”的行。
2
3
4
这个过程会进行两次:针对
Type1
和
Type2
。
Type1
和
Type2
的结果将在
Value
上进行
FULL JOIN
。
最后,一个间隙和区间分组通过将间隔/折叠回来。
逐步运行查询,按CTE进行检查中间结果以了解其工作原理。
样本数据
我添加了一些行,以说明当值之间存在间隙时的情况。
DECLARE @Table1 TABLE
([Start] int, [End] int, [Type1] varchar(4), [Type2] varchar(4))
;
INSERT INTO @Table1 ([Start], [End], [Type1], [Type2]) VALUES
( 0, 2, 'L', NULL),
( 2, 3, NULL, 'S'),
( 2, 5, 'L', NULL),
( 3, 5, NULL, 'S'),
( 5, 7, 'L', NULL),
( 5, 8, NULL, 'S'),
( 7, 10, 'L', NULL),
(11, 12, NULL, 'S'),
(15, 20, 'L', NULL),
(15, 20, NULL, 'S');
Query
WITH
e1(n) AS
(
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1
)
,e2(n) AS (SELECT 1 FROM e1 CROSS JOIN e1 AS b)
,e3(n) AS (SELECT 1 FROM e1 CROSS JOIN e2)
,CTE_Numbers
AS
(
SELECT ROW_NUMBER() OVER (ORDER BY n) AS Number
FROM e3
)
,CTE_T1
AS
(
SELECT
T1.[Start] + CA.Number - 1 AS Value
,T1.Type1
FROM
@Table1 AS T1
CROSS APPLY
(
SELECT TOP(T1.[End] - T1.[Start]) CTE_Numbers.Number
FROM CTE_Numbers
ORDER BY CTE_Numbers.Number
) AS CA
WHERE
T1.Type1 IS NOT NULL
)
,CTE_T2
AS
(
SELECT
T2.[Start] + CA.Number - 1 AS Value
,T2.Type2
FROM
@Table1 AS T2
CROSS APPLY
(
SELECT TOP(T2.[End] - T2.[Start]) CTE_Numbers.Number
FROM CTE_Numbers
ORDER BY CTE_Numbers.Number
) AS CA
WHERE
T2.Type2 IS NOT NULL
)
,CTE_Values
AS
(
SELECT
ISNULL(CTE_T1.Value, CTE_T2.Value) AS Value
,CTE_T1.Type1
,CTE_T2.Type2
,ROW_NUMBER() OVER (ORDER BY ISNULL(CTE_T1.Value, CTE_T2.Value)) AS rn
FROM
CTE_T1
FULL JOIN CTE_T2 ON CTE_T2.Value = CTE_T1.Value
)
,CTE_Groups
AS
(
SELECT
Value
,Type1
,Type2
,rn
,ROW_NUMBER() OVER
(PARTITION BY rn - Value, Type1, Type2 ORDER BY Value) AS rn2
FROM CTE_Values
)
SELECT
MIN(Value) AS [Start]
,MAX(Value) + 1 AS [End]
,Type1
,Type2
FROM CTE_Groups
GROUP BY rn-rn2, Type1, Type2
ORDER BY [Start];
Result
+
| Start | End | Type1 | Type2 |
+
| 0 | 2 | L | NULL |
| 2 | 8 | L | S |
| 8 | 10 | L | NULL |
| 11 | 12 | NULL | S |
| 15 | 20 | L | S |
+