日期:2014-05-17 浏览次数:20936 次
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