e.g,
foo1
foo2
foo10
foo100
与其;而不是
foo1
foo10
foo100
foo2
更新:我对自己编写排序代码不感兴趣(虽然这本身很有趣),但我希望数据库为我进行排序。
您可以在order-by子句中使用函数。在这种情况下,您可以将字段的非数字和数字部分分开,并将它们用作两个排序标准之一。
select * from t
order by to_number(regexp_substr(a,'^[0-9]+')),
to_number(regexp_substr(a,'[0-9]+$')),
a;
create index t_ix1
on t (to_number(regexp_substr(a, '^[0-9]+')),
to_number(regexp_substr(a, '[0-9]+$')),
a);
如果“数字”的数量和最大长度有限制,那么可以使用基于正则表达式的解决方案。
思路如下:
假设:
lpad('1 ', 3000, '1 ')
将失败,因为无法将填充后的数字适配到varchar2(4000)
中)以下查询已经针对“短数字”进行了优化(参见*?
),它只需要0.4秒。但是,当使用这种方法时,您需要预定义填充长度。
select * from (
select dbms_random.string('X', 30) val from xmltable('1 to 1000')
)
order by regexp_replace(regexp_replace(val, '(\d+)', lpad('0', 20, '0')||'\1')
, '0*?(\d{21}(\D|$))', '\1');
虽然单独的natural_sort
函数可能很方便,但是在纯SQL中有一个鲜为人知的技巧可以实现。
关键思路:
02
按顺序排列在1
和3
之间:regexp_replace(val, '(^|\D)0+(\d+)', '\1\2')
。注意:这可能会导致10.02
>10.1
的“意外”排序(因为02
转换为2
),但是对于像10.02.03
这样的东西应该如何排序没有一个单一的答案。"
转换为""
,以便带引号的文本正常工作'"'||regexp_replace(..., '([^0-9]+)', '","\1","')||'"'
xmltable
将csv转换为项目列表length(length(num))||length(num)||num
而不是lpad(num, 10, '0')
,因为后者不够紧凑且不支持11位或更多位的数字。注意:对于30个随机字符串的列表进行排序时,响应时间大约为3-4秒(生成随机字符串本身需要0.2秒)。
主要的时间消耗者是xmltable
将文本拆分成行的过程。
如果使用PL/SQL代替xmltable
将字符串拆分为行,则相同的1000行的响应时间降至0.4秒。
以下查询对100个随机字母数字字符串进行自然排序(注意:在Oracle 11.2.0.4中,它会产生错误的结果,在12.1.0.2中可以正常工作):
select *
from (
select (select listagg(case when regexp_like(w, '^[0-9]')
then length(length(w))||length(w)||w else w
end
) within group (order by ord)
from xmltable(t.csv columns w varchar2(4000) path '.'
, ord for ordinality) q
) order_by
, t.*
from (
select '"'||regexp_replace(replace(
regexp_replace(val, '(^|\D)0+(\d+)', '\1\2')
, '"', '""')
, '([^0-9]+)', '","\1","')||'"' csv
, t.*
from (
select dbms_random.string('X', 30) val from xmltable('1 to 100')
) t
) t
) t
order by order_by;
有趣的是,这个 order by
可以不使用子查询来表达,所以它是一个很方便的工具,可以让您的审核人员发疯(它适用于 11.2.0.4 和 12.1.0.2):
select *
from (select dbms_random.string('X', 30) val from xmltable('1 to 100')) t
order by (
select listagg(case when regexp_like(w, '^[0-9]')
then length(length(w))||length(w)||w else w
end
) within group (order by ord)
from xmltable('$X'
passing xmlquery(('"'||regexp_replace(replace(
regexp_replace(t.val, '(^|\D)0+(\d+)', '\1\2')
, '"', '""')
, '([^0-9]+)', '","\1","')||'"')
returning sequence
) as X
columns w varchar2(4000) path '.', ord for ordinality) q
);
我使用以下函数来将值中所有长度小于10的数字序列填充为0,以使每个数字序列的总长度变为10位。它甚至与具有一个、多个或没有数字序列的混合值集兼容。
CREATE OR replace function NATURAL_ORDER(
P_STR varchar2
) return varchar2
IS
/** --------------------------------------------------------------------
Replaces all sequences of numbers shorter than 10 digits by 0-padded
numbers that exactly 10 digits in length. Usefull for ordering-by
using NATURAL ORDER algorithm.
*/
l_result varchar2( 32700 );
l_len integer;
l_ix integer;
l_end integer;
begin
l_result := P_STR;
l_len := LENGTH( l_result );
l_ix := 1;
while l_len > 0 loop
l_ix := REGEXP_INSTR( l_result, '[0-9]{1,9}', l_ix, 1, 0 );
EXIT when l_ix = 0;
l_end := REGEXP_INSTR( l_result, '[^0-9]|$', l_ix, 1, 0 );
if ( l_end - l_ix >= 10 ) then
l_ix := l_end;
else
l_result := substr( l_result, 1, l_ix - 1 )
|| LPAD( SUBSTR( l_result, l_ix, l_end-l_ix ), 10, '0' )
|| substr( l_result, l_end )
;
l_ix := l_ix + 10;
end if;
end loop;
return l_result;
end;
/
例如:
select 'ABC' || LVL || 'DEF' as STR
from (
select LEVEL as LVL
from DUAL
start with 1=1
connect by LEVEL <= 35
)
order by NATURAL_ORDER( STR )