with t as ( select '1' b from dual union all select '+2' b from dual union all select '+1' b from dual union all select '2' b from dual union all select '1+' b from dual )select t.b from t order by decode(instr(t.b,'+'),0,1,1,3,2),replace(t.b,'+','')
临时拼凑出来的,你可以先将就下,哈哈WITH tt AS( SELECT '1' AS A FROM DUAL UNION ALL SELECT '+1' FROM DUAL UNION ALL SELECT '2' FROM DUAL UNION ALL SELECT '2+' FROM DUAL UNION ALL SELECT '3' FROM DUAL UNION ALL SELECT '4' FROM DUAL UNION ALL SELECT '+5' FROM DUAL UNION ALL SELECT '5' FROM DUAL UNION ALL SELECT '5+' FROM DUAL ) SELECT * FROM (SELECT * FROM tt WHERE LENGTH(A) = 1 ORDER BY A ASC) K UNION ALL SELECT * FROM (SELECT * FROM tt WHERE LENGTH(A) = 2 AND A NOT LIKE '+%' ORDER BY A ASC)M UNION ALL SELECT * FROM (SELECT * FROM tt WHERE LENGTH(A) = 2 AND A LIKE '+%' ORDER BY A ASC)L
WITH T AS( SELECT '1' AS A FROM DUAL UNION ALL SELECT '+1' FROM DUAL UNION ALL SELECT '22' FROM DUAL UNION ALL SELECT '22+' FROM DUAL UNION ALL SELECT '3' FROM DUAL UNION ALL SELECT '4' FROM DUAL UNION ALL SELECT '+5' FROM DUAL UNION ALL SELECT '5' FROM DUAL UNION ALL SELECT '5+' FROM DUAL ) select * from (SELECT a,to_number(regexp_substr(a,'[0-9]+')) o2,decode(instr(a,'+'),0,1,1,3,2) O1 from T) order by o1,o2
WITH T AS( SELECT '1' AS A FROM DUAL UNION ALL SELECT '+1' FROM DUAL UNION ALL SELECT '2' FROM DUAL UNION ALL SELECT '2+' FROM DUAL UNION ALL SELECT '3' FROM DUAL UNION ALL SELECT '4' FROM DUAL UNION ALL SELECT '+5' FROM DUAL UNION ALL SELECT '5' FROM DUAL UNION ALL SELECT '5+' FROM DUAL ) SELECT a from T ORDER BY length(a) ,REPLACE(a,'+','@')
必须特殊处理:先根据子段长度来区分(假如只有2位,多了还真不会),先把长度是1的找出来排序,使用UNION ALL把其他的连上
with t as (
select '1' b from dual
union all
select '+2' b from dual
union all
select '+1' b from dual
union all
select '2' b from dual
union all
select '1+' b from dual
)select t.b from t order by decode(instr(t.b,'+'),0,1,1,3,2),replace(t.b,'+','')
SELECT '1' AS A FROM DUAL
UNION ALL
SELECT '+1' FROM DUAL
UNION ALL
SELECT '2' FROM DUAL
UNION ALL
SELECT '2+' FROM DUAL
UNION ALL
SELECT '3' FROM DUAL
UNION ALL
SELECT '4' FROM DUAL
UNION ALL
SELECT '+5' FROM DUAL
UNION ALL
SELECT '5' FROM DUAL
UNION ALL
SELECT '5+' FROM DUAL
)
SELECT * FROM (SELECT * FROM tt WHERE LENGTH(A) = 1
ORDER BY A ASC) K
UNION ALL
SELECT * FROM (SELECT * FROM tt WHERE LENGTH(A) = 2 AND A NOT LIKE '+%'
ORDER BY A ASC)M
UNION ALL
SELECT * FROM (SELECT * FROM tt WHERE LENGTH(A) = 2 AND A LIKE '+%'
ORDER BY A ASC)L
WITH T AS(
SELECT '1' AS A FROM DUAL
UNION ALL
SELECT '+1' FROM DUAL
UNION ALL
SELECT '22' FROM DUAL
UNION ALL
SELECT '22+' FROM DUAL
UNION ALL
SELECT '3' FROM DUAL
UNION ALL
SELECT '4' FROM DUAL
UNION ALL
SELECT '+5' FROM DUAL
UNION ALL
SELECT '5' FROM DUAL
UNION ALL
SELECT '5+' FROM DUAL
)
select * from
(SELECT a,to_number(regexp_substr(a,'[0-9]+')) o2,decode(instr(a,'+'),0,1,1,3,2) O1 from T)
order by o1,o2
SELECT '1' AS A FROM DUAL
UNION ALL
SELECT '+1' FROM DUAL
UNION ALL
SELECT '2' FROM DUAL
UNION ALL
SELECT '2+' FROM DUAL
UNION ALL
SELECT '3' FROM DUAL
UNION ALL
SELECT '4' FROM DUAL
UNION ALL
SELECT '+5' FROM DUAL
UNION ALL
SELECT '5' FROM DUAL
UNION ALL
SELECT '5+' FROM DUAL
)
SELECT a from T ORDER BY length(a) ,REPLACE(a,'+','@')