1
1.1
1.1.1
1.1.2
1.2
...
1.10
1.10.1
...
2
2.1
...
如何排序呢?
order by后1.10等就会排在1.2的前面,怎么办呢?
解决方案:写个函数,把这个字符串换算成一个数字,然后排序。
比如
1=1*1000,
1.1=1*1000+1*100
1.10=1*1000+10*100
1.2=1*1000+2*100
order by function(字段)
具体实现:
create or replace function mywbs_sort(wbs in varchar2) return varchar2 is
Result varchar2(20);
restwbs varchar2(20);
pos int(32);
str varchar(2);
i int(32);
m int(32);
begin
Result := 0;
i := 1;
restwbs := wbs;
while length(restwbs) > 0 loop
pos := instr(restwbs, '.', 1);
if pos = 0 then
str := restwbs;
pos:=100; --为了让下一次restwbs取空,循环结束条件
else
str := substr(restwbs, 1, pos - 1);
end if;
if i = 1 then
m := 10000;
end if;
if i = 2 then
m := 1000;
end if;
if i = 3 then
m := 100;
end if;
if i = 4 then
m := 10;
end if;
Result := Result + to_number(str) * m;
restwbs := substr(restwbs, pos + 1); -- 默认截取到字符串的结尾
i := i + 1;
end loop;
return(Result);
end mywbs_sort;