按顺序的值分组 [英] Group by values that are in sequence

问题描述

我有这样的桌子

row chequeNo
 1     15
 2     19
 3     20
 4     35
 5     16

我需要得到这样的结果

row  from   to    
 1    15    16     
 2    19    20    
 3    35    35

所以我需要一组chequeNo，其中值将是连续的而不会受到任何干扰. chequeNo是唯一列.此外，应该使用一个sql选择查询来完成，因为除选择查询外，我无权创建任何sql结构.

so I need groups of chequeNo where values would be sequential without any interruptions. chequeNo is unique column. Additionally it should be done with one sql select query, because I haven't permissions to create any sql structures except select queries.

那有可能吗?

感谢您的帮助

推荐答案

您可以使用Aketi Jyuuzou的技术，称为 Tabibitosan 此处:

You can use Aketi Jyuuzou's technique called Tabibitosan here:

SQL> create table mytable (id,chequeno)
  2  as
  3  select 1, 15 from dual union all
  4  select 2, 19 from dual union all
  5  select 3, 20 from dual union all
  6  select 4, 35 from dual union all
  7  select 5, 16 from dual
  8  /

Table created.

SQL> with tabibitosan as
  2  ( select chequeno
  3         , chequeno - row_number() over (order by chequeno) grp
  4      from mytable
  5  )
  6  select row_number() over (order by grp) "row"
  7       , min(chequeno) "from"
  8       , max(chequeno) "to"
  9    from tabibitosan
 10   group by grp
 11  /

       row       from         to
---------- ---------- ----------
         1         15         16
         2         19         20
         3         35         35

3 rows selected.

关于，
罗布.

Regards,
Rob.

这篇关于按顺序的值分组的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！