sql-server - 在 SQL Server 中合并/扩展相同类型的记录

Question

我在将相同类型的记录与连续序列合并并从合并记录计算完整序列时遇到问题。

排序应根据 ID 进行，因为当序列达到 100 时，它们可能会翻转为 0。请参阅输入/输出示例中的最后一个条目。

是否有可能有一个如下所列的输入，并有一个查询产生的输出也在 SQL Server 2012 中列出？

输入

Id     Type     Begin     End
-----------------------------
1      1        10        20
2      1        21        23
3      2        24        28
4      1        29        40
5      2        41        47
6      2        48        50
7      2        75        80
8      1        81        100
9      1        0         10
10     1        11        20
11     1        21        5
12     1        5         6

输出

FromId     ToId     Type     Begin     End    Length
----------------------------------------------------
1          2        1        10        23     13 (23-19)
3          3        2        24        28     4  (28-24)
4          4        1        29        40     11 (40-29)
5          6        2        41        50     9  (50-41)
7          7        2        75        80     5  (80 - 75)
8          12       1        81        20     227*

*(100-81) + 10 + (100-11 + 20) + (100-21 + 5) + 1 -> 序列的翻转

编辑

请注意，源中的第 6 行和第 7 行未合并，因为它们不连续。第 6 行以 50 结束，第 7 行以 75 开始。只有相同类型的连续行才需要合并。

Answer 1

您最后一行的 Begin = 10 与其他人不遵循相同的规则。我在我的示例中将其更新为 11。希望这会有所帮助。

SQL Fiddler

WITH typeRowNum AS (  
  SELECT *, ROW_NUMBER() OVER (ORDER BY Id ASC) AS rownum
  FROM tblType
)
,rw AS (
  SELECT t1.*, 
    CASE WHEN t1.[type] = t2.[type] and ( t1.[Begin] = t2. [end] + 1 OR t1.[Begin] + 100 = t2.[end])
      THEN -1 
      ELSE t1.rownum 
    END AS group_id
  FROM typeRowNum t1
  LEFT JOIN typeRowNum t2 
    ON t2.rownum = t1.rownum - 1
 )
, cte AS (
SELECT *,
  new_end = ISNULL(
    (SELECT MIN(rownum) - 1 FROM rw r2 WHERE r2.rownum > r1.rownum and r2.group_id > r1.group_id),
    (SELECT MAX(rownum) FROM rw)
  )
FROM rw r1
WHERE r1.group_id > 0
 )
select 
  c1.id,c1.type,c1.[begin],c2.[end]
 ,[length] = (SELECT SUM((r.[end]  - r.[Begin]
                  + CASE WHEN r.[end] < r.[Begin] THEN 100 ELSE 0 END 
                  + CASE WHEN (r.group_id = -1) AND (r.[Begin] < r.[End]) THEN 1 ELSE 0 END)
                 ) 
             FROM rw r WHERE r.rownum  BETWEEN c1.[rownum] AND c2.[rownum])  
FROM cte c1
LEFT JOIN rw c2
  ON c1.new_end = c2.rownum

更新:如果您有 NULL 值，很可能您在 [Id] 列中有一些不连续的值。相反，您可以使用 Row_Number 进行 JOIN。我在上面更新了我的答案。