查找序列中的起始点和结束点

Question

我有一个表(T1)，其中有一列(C1)，其中包含以下值

1
2
3
5
6
8
9
10

我希望输出打印具有如下开始点和结束点的连续序列。

1-3
5-6
8-10

你能帮帮忙吗？

任何数据库都可以。

Answer 1

Oracle:首先是示例数据，而真正需要的代码从第11行开始。

SQL> with t1 (c1) as
  2    (select 1 from dual union all
  3     select 2 from dual union all
  4     select 3 from dual union all
  5     select 5 from dual union all
  6     select 6 from dual union all
  7     select 8 from dual union all
  8     select 9 from dual union all
  9     select 10 from dual
 10    )
 11  select min(c1), max(c1)
 12  from (select c1, c1 - row_number() over (order by c1) rn
 13        from t1
 14       )
 15  group by rn
 16  order by rn;

   MIN(C1)    MAX(C1)
---------- ----------
         1          3
         5          6
         8         10

SQL>

Answer 2

您可以使用以下查询。我已经在SQL Server上测试过它，但我认为它不需要在Oracle中进行修改就可以工作：

create table t1(c1 int);

insert into t1
 select *
   from (values(1),(2),(3),(5),(6),(8),(9),(10))t(x);

select case when count(*) >1 then 
             concat(min(c1),'-',max(c1)) 
            else concat(max(c1),'')
        end as concat_cs
from (
        select c1
               ,ROW_NUMBER() over(order by c1 asc)  as rnk
               ,c1 - ROW_NUMBER() over(order by c1 asc) as grp
          from t1
     )x
group by x.grp

输出

concat_cs
1-3
5-6
8-10

Answer 3

with stab as (
   select 1 as val from dual union all
   select 2 as val from dual union all 
   select 3 as val from dual union all
   select 5 as val from dual union all 
   select 6 as val from dual union all 
   select 8 as val from dual union all 
   select 9 as val from dual union all 
   select 10 as val from dual union all 
   select 13 as val from dual union all 
   select 15 as val from dual union all 
   select 16 as val from dual union all 
   select 17 as val from dual union all 
   select 18 as val from dual union all 
   select 19 as val from dual union all 
   select 23 as val from dual 
),sq2 as(
  select 
    row_number() over(order by 1) as rownumber,val
  from stab
)
  select 
    a.val,b.val
  from sq2 A
  join sq2 b on b.rownumber  = a.rownumber+2 
  where mod(A.rownumber,3)=1

输出：

         1          3
         5          8
         9         13
        15         17
        18         23