如何计算连续区间,字母分段

【问题】

I have a table called rbd_dtl. The table structure as follows...
RBD_ID ODFEE_ID RBD_VALUE
25 206 ALL 
31 205 A
26 205 B
41 205 C
42 205 D
30 205 E
38 205 F
39 205 H
27 205 J
35 205 K
32 205 N
36 205 O
28 205 Q
44 205 R
34 205 S
29 205 U
33 205 V
40 205 X
37 205 Y
43 205 Z
My requirement is to write a PLSQL Function which returns a character...
The column RBD_VALUE contains values as ALL or from A to Z.
If the column value as all then i return the same
If not as you can see the data above there in the table, the following alphabets are missing for 205 odfee_id...
G, I, L, M, P, T, W
So it should return as A-F,H,J-K,N-O,Q-S,U-V,X-Z
Based on the above data form A to F we can see all consecutive alphabets but after F there is no G so we have to show as A-F.
The next is H and there is no I which is missing so we pick H
After H, I is missing so we see it from J, K and after this there is no consecutive character so we pick as J-K

【回答】

       窗口函数可以实现字母分段,但实现方式较难理解。如果希望代码更简洁,可以考虑用SPL来实现,脚本如下:


A

1

$select RBD_VALUE from rbd_dtl

2

=A1.(RBD_VALUE).align(26.(char(64+~)))

3

=A2.group@o(!~)

4

=A3.select(~(1))

5

=A4.(~(1)+if(~.len()>1,"-"+~.m(-1))).concat@c()

A1:读取数据

A2:将RBD_VALUE按着大写26个字母对位

A3:归并分组,将相邻字母连续的成员分为一组

A4:选出非空的组

undefined

A5:将每组的首尾字母选出用“-”连接,每组用逗号分隔拼成串返回,结果如下:

undefined

写好的脚本如何在应用程序中调用,可以参考Java 如何调用 SPL 脚本