问如何使用oracle 11g中的sql查询实现以下输出。

EN

SQL> select * from data order by block_id;

  BLOCK_ID      VALUE
---------- ----------
         1          5
         2          5
         3          5
         4          0
         5          0
         6          4
         7          4
         9          5
        10          5
        12          2

SQL> select min(block_id) || '-' || max(block_id) block_range, value
  2    from (select block_id, value, max(grp) over (partition by value order by block_id) grp
  3            from (select block_id, value,
  4                         case
  5                           when lag(block_id) over (partition by value order by block_id) < block_id - 1
  6                           then
  7                             row_number() over (partition by value order by block_id)
  8                           when row_number() over (partition by value order by block_id) = 1 then 1
  9                           else null
 10                         end grp
 11                    from data
 12                   where value != 0))
 13   group by value, grp
 14   order by min(block_id);

BLOCK_RANG      VALUE
---------- ----------
1-3                 5
6-7                 4
9-10                5
12-12               2

CREATE TABLE test(
    a   INTEGER,
    b   INTEGER
);

INSERT INTO test VALUES (1, 5);
INSERT INTO test VALUES (2, 5);
INSERT INTO test VALUES (3, 5);
INSERT INTO test VALUES (4, 0);
INSERT INTO test VALUES (5, 0);
INSERT INTO test VALUES (6, 4);
INSERT INTO test VALUES (7, 4);

select min(a) || '-' || max(a) from test group by b order by 1;

select distinct
        min(block_id) over (partition by value) || '-' ||
        max(block_id) over (partition by value)
   from whatever_your_table_is_called
   where value > 0;