SQL - 合并重叠数据

Question

我在 SQL Server 中有一个简单的数据集，如下所示

**ROW    Start    End**
  0     1        2
  1     3        5
  2     4        6
  3     8        9

从图形上看，数据将如下所示

我想要实现的是折叠重叠数据，以便我的查询返回

**ROW    Start    End**
  0     1        2
  1     3        6
  2     8        9

这在 SQL Server 中是否可行，而无需编写复杂的过程或语句？

Answer 1

这是SQL Fiddle 另一种选择。

首先，所有限制均按顺序排序。然后，删除重叠范围内的“重复”限制(因为一个开始后面跟着另一个开始，或者一个结束后面跟着另一个结束)。现在，范围已折叠，开始值和结束值将再次写在同一行中。

with temp_positions as  --Select all limits as a single column along with the start / end flag (s / e)
(
    select startx limit, 's' as pos from t
    union
    select endx, 'e' as pos from t
)
, ordered_positions as --Rank all limits
(
    select limit, pos, RANK() OVER (ORDER BY limit) AS Rank
    from temp_positions
)
, collapsed_positions as --Collapse ranges (select the first limit, if s is preceded or followed by e, and the last limit) and rank limits again
(
    select op1.*, RANK() OVER (ORDER BY op1.Rank) AS New_Rank
    from ordered_positions op1
    inner join ordered_positions op2
    on (op1.Rank = op2.Rank and op1.Rank = 1 and op1.pos = 's')
    or (op2.Rank = op1.Rank-1 and op2.pos = 'e' and op1.pos = 's') 
    or (op2.Rank = op1.Rank+1 and op2.pos = 's' and op1.pos = 'e')
    or (op2.Rank = op1.Rank and op1.pos = 'e' and op1.Rank = (select max(Rank) from ordered_positions))
)
, final_positions as --Now each s is followed by e. So, select s limits and corresponding e limits. Rank ranges
(
    select cp1.limit as cp1_limit, cp2.limit as cp2_limit,  RANK() OVER (ORDER BY cp1.limit) AS Final_Rank
    from collapsed_positions cp1
    inner join collapsed_positions cp2
    on cp1.pos = 's' and cp2.New_Rank = cp1.New_Rank+1
)
--Finally, subtract 1 from Rank to start Range #'s from 0
select fp.Final_Rank-1 seq_no, fp.cp1_limit as starty, fp.cp2_limit as endy
from final_positions fp;

您可以测试每个 CTE 的结果并跟踪进展情况。您可以通过删除以下 CTE 并从前一个 CTE 中进行选择来完成此操作，如下所示。

with temp_positions as  --Select all limits as a single column along with the start / end flag (s / e)
(
    select startx limit, 's' as pos from t
    union
    select endx, 'e' as pos from t
)
, ordered_positions as --Rank all limits
(
    select limit, pos, RANK() OVER (ORDER BY limit) AS Rank
    from temp_positions
)
select *
from ordered_positions;