Oracle：如何在SQL查询中实现“自然”排序？

Question

e.g,

foo1
foo2
foo10
foo100

与其；而不是

foo1
foo10
foo100
foo2

更新：我对自己编写排序代码不感兴趣（虽然这本身很有趣），但我希望数据库为我进行排序。

- Mark Harrison

---

Jeff也在这个主题上发表了一篇文章，提供其他语言的更多资源。 - Tom Ritter

Answer 1

您可以在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);

- Mark Harrison

---

如果你想要得到SQL的答案，你可能需要澄清你的问题以明确指定。或者你是想收集各种排序技术呢？ - Justin Voss

他同时发布了问题和答案。他可能是想分享他发现的这一点知识，收集声望分数，或两者兼而有之。 - postfuturist

不想失礼，但这被标记为SQL。 - kafuchau

即便如此，在问题中再多添加一些描述也无妨。 - Davy8

$[0-9]+ 不是匹配行末的数字吗？所以它总是为空吗？此外，如果有多组数字，则无法正常工作。 - Joseph Bui

Answer 2

针对短字符串和少量数字

如果“数字”的数量和最大长度有限制，那么可以使用基于正则表达式的解决方案。

思路如下：

• 用20个零填充所有数字
• 使用另一个正则表达式去除多余的零。由于正则表达式回溯，这可能会很慢。

假设：

• 数字的最大长度是预先知道的（例如20）
• 所有数字都可以填充（换句话说，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
);

- Vladimir Sitnikov

---

必须表现出色... :-) - Lukas Eder

Answer 3

我使用以下函数来将值中所有长度小于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 )

- Coder

---

聪明！谢谢分享。虽然我怀疑这不是特别高效的，而且当更大的整数超过10位时会显然失效。但是还是很有趣。 - ddevienne