ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 号码连续分段问题(2)

号码连续分段问题(2)

原创 Linux操作系统 作者:cow977 时间:2012-04-27 11:26:27 0 删除 编辑
二、运行SQL及结果:

SQL> select min(id) || '-' || max(id) flag, card_type, region

  2   from

  3   (

  4   select id, card_type, region,

  5      id-row_number() over(partition by card_type, region order by id) flag

  6   from tab1

  7   )

  8   group by card_type, region, flag

  9   order by 1;

 

FLAG                                      CARD_TYPE REGION

---------------------------------------- ---------- ----------

100-101                                          11 S

102-104                                          11 N

105-107                                           0 S

108-110                                           0 N

111-112                                          11 S

113-115                                          11 N

116-117                                           0 S

118-120                                           0 N

121-130                                          11 S

200-201                                          11 S

202-204                                          11 N

205-207                                           0 S

208-210                                           0 N

211-212                                          11 S

213-215                                          11 N

300-301                                          11 S

302-304                                          11 N

305-307                                           0 S

308-310                                           0 N

311-312                                          11 S

313-315                                          11 N

 

21 rows selected

 

Execution Plan

----------------------------------------------------------

Plan hash value: 2779934906

 

----------------------------------------------------------------------

|Id| Operation             |Name|Rows| Bytes | Cost (%CPU)| Time     |

----------------------------------------------------------------------

| 0| SELECT STATEMENT      |    | 63 |  2898 |     6  (50)| 00:00:01 |

| 1|  SORT ORDER BY        |    | 63 |  2898 |     6  (50)| 00:00:01 |

| 2|   HASH GROUP BY       |    | 63 |  2898 |     6  (50)| 00:00:01 |

| 3|    VIEW               |    | 63 |  2898 |     4  (25)| 00:00:01 |

| 4|     WINDOW SORT       |    | 63 |  2079 |     4  (25)| 00:00:01 |

| 5|      TABLE ACCESS FULL|TAB1| 63 |  2079 |     3   (0)| 00:00:01 |

----------------------------------------------------------------------

 

Statistics

----------------------------------------------------------

          4  recursive calls

          0  db block gets

         15  consistent gets

          0  physical reads

          0  redo size

       1221  bytes sent via SQL*Net to client

        531  bytes received via SQL*Net from client

          3  SQL*Net roundtrips to/from client

          2  sorts (memory)

          0  sorts (disk)

         21  rows processed

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/81227/viewspace-722546/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2011-03-02

  • 博文量
    629
  • 访问量
    686547