Oracle字符串字段内的字符排序
这个是狙狙的sql解法。
http://blog.csdn.net/happyflystone/archive/2009/01/17/3819863.aspx
引用需求
今天和梁翁在群里聊天，小家伙突然抛出一个有意思的问题，那就是字符串字段内的字符串排序问题，比如有列 col, 有数据 'RDGS' , 要求输出为 'DGRS' 。
oracle分析函数支持聚合:)
create table t_test(f varchar2 ( 10 ));
insert into t_test values ( 'sdffesa' );
insert into t_test values ( 'asdflkj' );
insert into t_test values ( 'ijf92' );
select f, max ( replace (SYS_CONNECT_BY_PATH(c, ' ' ), ' ' , '' ))f1 from (
select f,rn,c,row_number()over( partition by f order by c) as ord from ( select
f,decode(rn,
1 ,substr(f, 1 , 1 ),
2 ,substr(f, 2 , 1 ),
3 ,substr(f, 3 , 1 ),
4 ,substr(f, 4 , 1 ),
5 ,substr(f, 5 , 1 ),
6 ,substr(f, 6 , 1 ),
7 ,substr(f, 7 , 1 ),
8 ,substr(f, 8 , 1 ),
9 ,substr(f, 9 , 1 ),
10 ,substr(f, 10 , 1 )
) as c,rn from
t_test a ,(
select level rn from dual connect by 1 = 1 and level <= 10 )b
where length(a.f)>=b.rn))
start with ord= 1 connect by f= prior f and ord- 1 = prior ord
group by f;
drop table t_test;
/*
F F1
ijf92 29fij
asdflkj adfjkls
sdffesa adeffss
*/
后来受到小梁的启发，修改了一下拆分字符串的方法，可以把decode去掉
-- 测试环境
create table t_test(f varchar2 ( 10 ));
insert into t_test values ( 'sdffesa' );
insert into t_test values ( 'asdflkj' );
insert into t_test values ( 'ijf92' );
-- 测试字符串的拆分
select
f,substr(f,rn, 1 ) as c from
t_test a ,(
select level rn from dual connect by 1 = 1 and level <= 10 )b
where length(a.f)>=b.rn order by f;
/*
F C
asdflkj a
asdflkj f
asdflkj j
asdflkj d
asdflkj l
asdflkj k
asdflkj s
ijf92 f
ijf92 2
ijf92 i
ijf92 9
ijf92 j
sdffesa a
sdffesa s
sdffesa e
sdffesa f
sdffesa d
sdffesa s
sdffesa f
*/
-- 测试拆分－聚合的完整语句
select f, max ( replace (SYS_CONNECT_BY_PATH(c, ' ' ), ' ' , '' ))f1 from (
select f,c,row_number()over( partition by f order by c) as ord from ( select
f,substr(f,rn, 1 ) as c from
t_test a ,(
select level rn from dual connect by 1 = 1 and level <= 10 )b
where length(a.f)>=b.rn))
start with ord= 1 connect by f= prior f and ord- 1 = prior ord
group by f;
drop table t_test;
/*
F F1
ijf92 29fij
asdflkj adfjkls
sdffesa adeffss
*/
