问使用子组限制创建行组

EN

select
    min(box_start) as box_start,
    box_end,
    box_label,
    is_box,
    item_code,
    item
from
    (
        select
            box_start,
            box_end,
            box_label,
            is_box,
            item_code,
            item
        from
            table1
        where
            box_label is not null

        union all

        select
            table1.box_start as box_start,
            table1.box_end as box_end,
            intervals.A || '-' || intervals.B as box_label,
            table1.is_box as is_box,
            table1.item_code as item_code,
            table1.item as item
        from
            (
                select
                    box_start,
                    box_end,
                    box_label,
                    is_box,
                    A,
                    B,
                    max(max_interval_size) as max_interval_size
                from
                    (
                        select
                            box_start,
                            box_end,
                            box_label,
                            is_box,
                            A,
                            B,
                            max(interval_size) as max_interval_size
                        from
                            (
                                select
                                  fixed_table.box_start as box_start,
                                  fixed_table.box_end as box_end,
                                  fixed_table.box_label as box_label,
                                  fixed_table.is_box as is_box,
                                  fixed_table.box_start as A,
                                  windowed_table.box_end as B,
                                  (windowed_table.box_end - fixed_table.box_start) as interval_size
                                from
                                  table1 fixed_table
                                  join table1 windowed_table on
                                    fixed_table.box_start <= windowed_table.box_end
                                where 
                                  interval_size >= 0
                                  and fixed_table.box_label is null
                                  and windowed_table.box_label is null
                                  and fixed_table.is_box = 'FALSE'
                                  and windowed_table.is_box = 'FALSE'

                                except

                                select
                                  without_a_box.*
                                from
                                    (
                                        select
                                          fixed_table.box_start as box_start,
                                          fixed_table.box_end as box_end,
                                          fixed_table.box_label as box_label,
                                          fixed_table.is_box as is_box,
                                          fixed_table.box_start as A,
                                          windowed_table.box_end as B,
                                          (windowed_table.box_end - fixed_table.box_start) as interval_size
                                        from
                                          table1 fixed_table
                                          join table1 windowed_table on
                                            fixed_table.box_start <= windowed_table.box_end
                                        where 
                                          interval_size >= 0
                                          and fixed_table.box_label is null
                                          and windowed_table.box_label is null
                                          and fixed_table.is_box = 'FALSE'
                                          and windowed_table.is_box = 'FALSE'
                                    ) as without_a_box
                                    ,
                                    (
                                        select distinct
                                            with_box.box_start as start_with_box
                                         from
                                            table1 with_box
                                         where
                                            with_box.is_box = 'FALSE'
                                            and with_box.box_label is not null
                                    ) as items_inside_a_box
                                where
                                    items_inside_a_box.start_with_box > without_a_box.A
                                    and items_inside_a_box.start_with_box < without_a_box.B
                            ) as without_intervals_that_intersect_boxed_items
                        group by
                            A
                    ) as final
                group by
                    B
            ) as intervals
            join table1 on
                table1.box_start >= intervals.A
                and table1.box_end <= intervals.B
                and table1.box_label is null
    )
group by
    box_label,
    is_box,
    item_code,
    item
order by
    box_start,
    item_code