sql - 如何组合连续的数字范围

Question

我正在寻找一种巧妙的方法来在单个 select 语句中组合连续的数字范围。

假设我的表有这些记录:

first_number    last_number
0   9
10  19
20  29
40  49
50  59
70  79

然后输出应如下:

first_number    last_number
0   29
40  59
70  79

这是我想出的:

select first_number, last_number_of_range
from
(
  select 
    first_number, is_continuing, is_continued,
    nvl(lead (last_number,1,null) over (order by first_number), last_number) as last_number_of_range
  from
  (
    select *
    from
    (
      select first_number, last_number, 
       case when lag (last_number,1,null) over (order by first_number) + 1 = first_number then 1 else 0 end as is_continuing, 
       case when lead (first_number,1,null) over (order by last_number) - 1 = last_number then 1 else 0 end as is_continued
      from 
      (
        select 0 as first_number, 9 as last_number from dual
        union all
        select 10 as first_number, 19 as last_number from dual
        union all
        select 20 as first_number, 29 as last_number from dual
        union all
        select 40 as first_number, 49 as last_number from dual
        union all
        select 50 as first_number, 59 as last_number from dual
        union all
        select 70 as first_number, 79 as last_number from dual
      )
    )
    where is_continuing = 0 or is_continued = 0 -- remove all but first and last of consecutive records
  )
)
where is_continuing = 0 -- now at last remove those records that gave us the last_number_of_range
;

这工作正常。只是，对于这么小的任务来说，它看起来如此复杂。我很想知道是否有比我更直接的方法。

Answer 1

这是另一种方法，它将为您提供所需的输出。

select min(first_number)  as first_number
     , max(last_number)   as last_number
 from ( 
        select first_number
              , last_number
              , sum(grp) over(order by first_number) as grp
           from ( select first_number
                       , last_number
                       , case
                           when first_number <>
                                   lag(last_number) 
                                     over(order by first_number) + 1
                           then 1
                           else 0
                         end as grp
                     from t1 )
       )
group by grp
order by 1

结果:

FIRST_NUMBER LAST_NUMBER
------------ -----------
           0          29
          40          59
          70          79

SQLFiddle demo