省份代码 类型
sf hm
2021 00000001
2021 00000002
2021 00000003
2021 00000004
2021 00000005
2021 00000007
2021 00000008
2021 00000009
2022 00000120
2022 00000121
2022 00000122
2022 00000124
2022 00000125
第一个字段是省份代码,第二个字段是分配的不同的手机号码段的前缀,第二个字段内可能是连续的数据,可能存在断点。
怎样能根据省份分组,把相同省份的连续的号码段合并起来,结果就像下⾯的这样:
2021,00000001,00000005
2021,00000009,00000007
2022,00000120,00000122
2022,00000124,00000125
在网上搜了一下,发现有如下办法实现:
with db as(
select 2021 as sf,'00000001' as hm
union
select 2021,'00000002'
union
select 2021,'00000003'
union
select 2021,'00000004'
union
select 2021,'00000005'
union
select 2021,'00000007'
union
select 2021,'00000008'
union
select 2021,'00000009'
union
select 2022,'00000120'
union
select 2022,'00000121'
union
select 2022,'00000122'
union
select 2022,'00000124'
union
select 2022,'00000125'
)
SELECT b.sf, MIN (b.hm) Start_HM, MAX (b.hm) End_HM,cast(wm_concat(b.hm) as varchar(1000)) as hms
FROM
(SELECT a.*, TO_NUMBER(a.hm - ROWNUM) cc FROM (SELECT * FROM db ORDER BY sf, hm) a) b
GROUP BY b.sf, b.cc;