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博客 ” ，链接：https://blog.itpub.net/81227/viewspace-722548/ ，如需转载，请注明出处，否则将追究法律责任。
