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