sql - 通过从两个(从和到)值计算非中断期间进行分组

Question

可以使用哪种 Oracle SQL 技术来连接这些行:

id | from | to
--------------
 a |   20 | 25
 a |   26 | 30
 a |   32 | 40
 b |    1 |  5
 b |    7 | 10
 b |   11 | 20

对于这样的结果:

 a |   20 | 30
 a |   32 | 40
 b |    1 |  5
 b |    7 | 20

?假设 from和 to是整数周期的开始和结束，必须为 id 选择一个不间断的周期的

只是寻找正确的方向和例子，这可以使用 group by 来完成吗？或 connect by还是别的什么？

数据库是 Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - 64bit Production

Answer 1

也许类似的东西:

select
    field_id,
    min(field_from),
    max(field_to)
from (
    select
        field_from,
        field_to,
        field_id,
        sum(willSum) over(partition by field_id order by field_from) as GID
    from (
        select
            field_from,
            field_to,
            field_id,
            case when field_from
                    = Lag(field_to) over(partition by field_id order by field_from)
                 then 0 else 1 end as willSum + 1
        from
            rj_mytest
        )
    )
group by
    field_id,
    GID
order by
    field_id,
    min(field_from);

还有几个类似的例子: https://forums.oracle.com/thread/969005