我需要在 oracle 中创建一个过程，并且必须实现以下逻辑

Question

我有一个带有列Tel_num的主表。Tel_num是像02365785431一样的 11 位数据。现在我必须取 tel_nums(substr( tel_nums，1,7((的前 7 位数字，并在数字按顺序排列时将最小值作为start_num，将最大值作为end_num。

如果出现以下情况：'

023158100001
023158100002
023158100003
023158100004
023158115645
023158111546
023158111547
023158111617
023158121110
023158121111

然后我想要这样的数字

Start_NUM                    End_NUM
023158100001                023158100004
023158115645                023158111547
023158111617                023158111617
023158121110                023158121111

我需要实现的这个东西.有人可以帮忙吗？？

Answer 1

不需要PL/SQL(如注释中所建议的(。关键字是间隙和孤岛(如果要搜索它(。

我修改了样本数据(因为它们是错误的，根据期望的结果(。

SQL> with test (tel) as
2  (select '023158100001' from dual union all
3   select '023158100002' from dual union all
4   select '023158100003' from dual union all
5   select '023158100004' from dual union all
6   select '023158111545' from dual union all
7   select '023158111546' from dual union all
8   select '023158111547' from dual union all
9   select '023158111617' from dual union all
10   select '023158121110' from dual union all
11   select '023158121111' from dual
12  ),
13  inter as
14  (select tel,
15          lag(tel) over (order by tel) lag_tel
16   from test
17  ),
18  inter_2 as
19  (select tel,
20          sum(case when nvl(tel - lag_tel, 1) = 1 then 0 else 1 end) over (order by tel) tel_group
21   from inter
22  )
23  select min(tel) min_tel,
24         max(tel) max_tel
25  from inter_2
26  group by tel_group
27  order by 1;
MIN_TEL      MAX_TEL
------------ ------------
023158100001 023158100004
023158111545 023158111547
023158111617 023158111617
023158121110 023158121111
SQL>

Answer 2

这将忽略问题的"前 7 位数字"部分，因为我认为这无关紧要。 我只会使用行号的差异来定义邻接关系：

select min(tel_num), max(tel_num)
from (select mt.*,
row_number() over (order by tel_num) as seqnum
from master_table
) t
group by (cast(tel_num as decimal(11, 0)) - seqnum);

如果您担心跨越前 7 位数字的序列，则只需将其包含在group by中：

select min(tel_num), max(tel_num)
from (select mt.*,
row_number() over (order by tel_num) as seqnum
from master_table
) t
group by substr(tel_num, 1, 7),
(cast(tel_num as decimal(11, 0)) - seqnum);

为什么会这样？ 当数字值递增 1 时，数字和序列之间的差异将是恒定的。 因此，当差值恒定时，数字是连续的。