这是SQL小提琴 http://www.sqlfiddle.com/#!6/96614/2另一种选择。
首先,所有限制按顺序排序。然后,删除重叠范围内的“重复”限制(因为一个开始后面跟着另一个开始,或者一个结束后面跟着另一个结束)。现在,范围已折叠,开始值和结束值将再次写在同一行中。
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;