1.背景,处理汽车厂商配件图号信息时,里面的图中编号数据出现了字母和数字,数字,小数点,数字加符号加数字的数据。
厂商要求排序时能兼顾这几种场景:
一,厂商A的配件信息中图中编号(字母和数字组合)。
----------------------------------------------------------
图中编号
---------------------------------------------------------
A10
---------------------------------------------------------
A11
---------------------------------------------------------
A12
---------------------------------------------------------
A13
---------------------------------------------------------
B10
---------------------------------------------------------
B11
---------------------------------------------------------
B12
---------------------------------------------------------
二,厂商B的配件信息中图中编号(数字,数字+符号(符号以小数点“.”或逗号“,”)+数字,(图中编号子编号,多个数字编号公用一个图号))注:多个数字编号公用一个图号的以第一个数字为主排序。
----------------------------------------------------------
图中编号
---------------------------------------------------------
1
---------------------------------------------------------
2
---------------------------------------------------------
3
---------------------------------------------------------
1.1
---------------------------------------------------------
1.2
---------------------------------------------------------
5
---------------------------------------------------------
4,7
---------------------------------------------------------
6
---------------------------------------------------------
8
---------------------------------------------------------
9
---------------------------------------------------------
10
---------------------------------------------------------
5.1
---------------------------------------------------------
5.2
---------------------------------------------------------
6.1
---------------------------------------------------------
11
---------------------------------------------------------
厂商期望的排序效果如下:
----------------------------------------------------------
图中编号
---------------------------------------------------------
1
---------------------------------------------------------
1.1
---------------------------------------------------------
1.2
---------------------------------------------------------
2
---------------------------------------------------------
3
---------------------------------------------------------
4,7
---------------------------------------------------------
5
---------------------------------------------------------
5.1
---------------------------------------------------------
5.2
---------------------------------------------------------
6
---------------------------------------------------------
6.1
---------------------------------------------------------
8
---------------------------------------------------------
9
---------------------------------------------------------
10
---------------------------------------------------------
11
---------------------------------------------------------
处理思路。由于是字符型的数据,要考虑如何兼顾不同的场景。
(一)业务场景
1、满足A厂商的字母和数字组合的;
2、满足B厂商的(数字,数字+符号(符号以小数点“.”或逗号“,”)+数字,(图中编号子编号,多个数字编号公用一个图号)。
(二)排序实现
1.字母排序优先;否则以数字优先的策略,A10和B10会排列在一起。
----------------------------------------------------------
图中编号
---------------------------------------------------------
A10
---------------------------------------------------------
B10
---------------------------------------------------------
A11
---------------------------------------------------------
B11
---------------------------------------------------------
A12
---------------------------------------------------------
B12
---------------------------------------------------------
A13
---------------------------------------------------------
2.麻烦的是B厂商的。要让符号前的数字先排序,在考虑符号后面的数字排序。
select t.* from (select p.IMG_IN_CODE,
decode(sign(trunc(regexp_substr(p.img_in_code,'[[:digit:]]+'),2)- trunc(regexp_substr(p.img_in_code,'[[:digit:]]+'))),1,trunc(regexp_substr(p.img_in_code,'[[:digit:]]+'),2),0,trunc(regexp_substr(p.img_in_code,'[[:digit:]]+'))) intcol,
case when instr(p.img_in_code,'.')!=0 then substr(p.img_in_code,instr(p.img_in_code,'.')+1) else '0' end colsort
from t_Parts_info p) t
order by regexp_substr(img_in_code,'[^0-9]+[.]{1}'),intcol,colsort
所用的函数简介:
一、decode
在Oracle/PLSQL中, decode 具有和 IF-THEN-ELSE 一样的功能。
decode 函数语法如下:
decode( expression , search , result [, search , result]... [, default] );
expression 要比较的表达式。
search 要与expression 比较的字段。.
result 如果expression 与search 一样的话,返回该结果。.
default 此参数可选,如果没有与expression 匹配上的search,就返回此结果,如果此参数没有设置,当没有与expression匹配上的search时,返回null。
search 和 result可成对出现多次,代表各种要匹配的情况。
应用于Oracle 9i, Oracle 10g, Oracle 11g。
举例:
select DECODE(t.sts,0,'启用',1,'禁用','未知状态') sts from t_user t
类似:if sts:=0 return '启用';
else if
sts:=1 return '
禁用';
else '未知状态';
二、sign
在Oracle/PLSQL中, sign 函数返回一个数字的正负标志.
语法如下:sign( number )
number 要测试标志的数字.
If number < 0, then sign returns -1.
If number = 0, then sign returns 0.
If number > 0, then sign returns 1.
应用于:Oracle 8i, Oracle 9i, Oracle 10g, Oracle 11g。
三、trunc(number)
在Oracle/PLSQL中, trunc function returns a number truncated to a certain number of decimal places.
trunc function 语法如下:
trunc( number, [ decimal_places ] )
number 要截取的数字。
decimal_places 要保留的小数位. 这个参数必须是个整数. 如果此参数缺省,默认保留0位小数
应用于:Oracle 8i, Oracle 9i, Oracle 10g, Oracle 11g
四、instr函数
InStr 函数
返回某字符串在另一字符串中第一次出现的位置。
InStr([start, ]string1, string2[, compare])
参数
start
可选项。数值表达式,用于设置每次搜索的开始位置。如果省略,将从第一个字符的位置开始搜索。如果 start 包含 Null,则会出现错误。如果已指定 compare,则必须要有 start 参数。
string1
必选项。接受搜索的字符串表达式。
string2
必选项。要搜索的字符串表达式。
compare
可选项。指示在计算子字符串时使用的比较类型的数值。有关数值,请参阅“设置”部分。如果省略,将执行二进制比较。
设置
compare 参数可以有以下值:
常数 | 值 | 描述 |
---|---|---|
vbBinaryCompare | 0 | 执行二进制比较。 |
vbTextCompare | 1 | 执行文本比较。 |
返回值
InStr 函数返回以下值:
如果 | InStr 返回 |
---|---|
string1 为零长度 | 0 |
string1 为 Null | Null |
string2 为零长度 | start |
string2 为 Null | Null |
string2 没有找到 | 0 |
在 string1 中找到 string2 | 找到匹配字符串的位置 |
start > Len(string2) | 0 |
五、case when cloumn then value else value end
case when 字段满足什么条件 then 显示值 else 其它显示值 end 字段别名;
SQL解释说明
1.decode(sign(trunc(regexp_substr(p.img_in_code,'[[:digit:]]+'),2)- trunc(regexp_substr(p.img_in_code,'[[:digit:]]+'))),1,trunc(regexp_substr(p.img_in_code,'[[:digit:]]+'),2),0,trunc(regexp_substr(p.img_in_code,'[[:digit:]]+'))) intcol 取数据的数字部分或符号前的数字。
2.case when instr(p.img_in_code,'.')!=0 then substr(p.img_in_code,instr(p.img_in_code,'.')+1) else '0' end colsort 取符号.后的数字。
排序时先排符号前的数字 intcol字段,再排列符号后的数字 colsort。按不同的字段处理。
3.order by regexp_substr(img_in_code,'[^0-9]+[.]{1}'),intcol,colsort
regexp_substr(img_in_code,'([A-Z|a-Z])+')用于正则排列字母部分,intcol 是处理后的符号前的数字,colsort是处理后的符号后的数字。