ITPub博客

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

号码连续分段问题(3)

原创 Linux操作系统 作者:cow977 时间:2012-04-28 11:29:19 0 删除 编辑
三、如果序号不唯一,有重复和跳号,结果就会不正确。

delete tab1;

insert into tab1 values (100, 11, ‘S’);

insert into tab1 values (101, 11, ‘S’);

insert into tab1 values (103, 11, ‘S’);

insert into tab1 values (103, 11, ‘S’);

insert into tab1 values (104, 11, ‘N’);

commit;

 

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-103                                          11 S

103-103                                          11 S

104-104                                          11 N

 

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

  2    from (

  3    select id, card_type, region, sum(flag) over(order by id) flag

  4    from (

  5    select id, card_type, region, nvl(id-lag(id, 1) over(order by id)

  6    + case when card_type = lag(card_type, 1) over(order by id) then 0 else 10 end

  7    + case when region = lag(region, 1) over(order by id) then 0 else 10 end

  8    , 1) -1 flag

  9    from tab1

 10    )

 11    )

 12    group by card_type, region, flag

 13    order by 1;

 

FLAG                                      CARD_TYPE REGION

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

100-103                                          11 S

104-104                                          11 N

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

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

注册时间:2011-03-02

  • 博文量
    629
  • 访问量
    683438