大家好，有一个问题请教各位：写一个select语句把下面的数据转换成如下的格式：

Question

大家好，有一个问题请教各位：写一个select语句把数据转换成最下面的格式：表结构:
 
CREATE TABLE inventory_num
   (inventory_num_id               NUMBER(15,0) NOT NULL primary key,
    inventory_group_id             NUMBER(15,0) NOT NULL,
    num                            NUMBER(15,0) NOT NULL,
    code                           VARCHAR2(10) NOT NULL)
 
数据:
 
inventory_num_id        inventory_group_id             num   code
 
         9681066                 100003894             211      E
         9679839                 100003894             212      E
         9687165                 100003894             213      E
         9680883                 100003894             214      I
         9710863                 100003894             515      E
         9681246                 100003894             516      E
         9682695                 100003894             517      E
         9681239                 100003894             518      E
         9685409                 100003894             519      E
         9679843                 100003894             520      C
         9679844                 100003894             521      C
         9714882                 100003894             522      E
         9679845                 100003894             523      I
         9681211                 100003894             524      E
         9681216                 100003894             525      E
         9682696                 100003894             526      E
         9681227                 100003894             527      E写一个select语句把数据转换成如下的格式：
inventory_group_id   code start   end 
 
         100003894      E   211   213
         100003894      I   214
         100003894      E   515   519
         100003894      C   520   521
         100003894      E   522
         100003894      I   523
         100003894      E   524   527

Answer 1

SELECT   inventory_group_id, code, MIN (num) star,
         CASE
            WHEN MIN (num) = MAX (num)
               THEN NULL
            ELSE MAX (num)
         END en
    FROM (SELECT   inventory_group_id, code, num
              FROM inventory_num
          ORDER BY inventory_group_id, code, num) a
GROUP BY inventory_group_id, code, ROWNUM - num
ORDER BY star