如何计算连续区间，字母分段
【问题】
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：选出非空的组
A5：将每组的首尾字母选出用“-”连接，每组用逗号分隔拼成串返回，结果如下：
写好的脚本如何在应用程序中调用，可以参考Java 如何调用 SPL 脚本
