日期:2014-05-17  浏览次数:20936 次

oracle,函数如何实现,列里的字符排序
请教一个问题,有一列存放的是字符型数据,
如:231
  628
  173
如何实现按数字顺序排序
结果为:
  123
  268
  137

------解决方案--------------------
SQL code

create table a3d1000 (id number(10),code varchar2(10));

insert into a3d1000 values (1,'231');
insert into a3d1000 values (2,'628'); 
insert into a3d1000 values (3,'185');
insert into a3d1000 values (4,'178');
insert into a3d1000 values (5,'173'); 
insert into a3d1000 values (6,'334');
insert into a3d1000 values (7,'111');
commit;

select t1.id,x||(case when replace(t2.code,y,'') is null then x  
                      when x=replace(t2.code,y,'') then y 
                      when y=replace(t2.code,x,'') then x 
                      else replace(replace(t2.code,x,''),y,'') end)||y code
from
  (select id,LEAST(LEAST(c1,c2),c3) x,greatest(greatest(c1,c2),c3) y
  from
      (select id,substr(code,1,1) c1, substr(code,2,1) c2, substr(code,3,1) c3
      from a3d1000
      )
  ) t1,a3d1000 t2
where t1.id=t2.id


     id    code
------------------------
1    1    123
2    2    268
3    3    158
4    4    178
5    5    137
6    6    334
7    7    111