从Oracle对记录进行排序，带有多个小数点(.) [英] Sorting records from Oracle with multiple decimal points (.)

问题描述

更新:

ORACLE版本10G

我在Oracle中有一个记录列表，如下所示，这些实际上是各种书籍的一部分
记录以以下格式生成

I have a list of records in Oracle as follows, these are actually sections of various books
The records are generated in the below format

[主要主题].[子主题].[第一级部分] ...... [最后一级部分]

Sections
--------
1
7.1
6.2 
7.1
7.4
6.8.3
6.8.2
10
1.1
7.6
6.1
11
8.3
8.5
1.1.2
6.4
6.6
8.4
1.1.6
6.8.1
7.7.1
7.5
7.3

我想像这样订购

 1
 1.1
 1.1.2
 1.1.6
 6.2    
 6.4    
 6.5    
 6.6    
 6.7    
 6.8.1    
 6.8.2    
 6.8.3    
 7.2    
 7.3    
 7.4    
 7.5    
 7.6    
 7.7.1    
 7.7.2    
 8.3    
 8.4    
 8.5
 10

但是由于该字段不是numeric datatype，因此排序结果如下:

But as the field is not a numeric datatype the sorting results in something like this

1
10
1.1
1.1.2
1.1.6
....
.....
8.5

如何对它们进行排序.由于多个小数点，我无法将它们转换为数字.

How can I sort them. I am unable to convert them to number due to multiple number of decimal points.

oracle中是否有任何支持这种排序技术的功能

Is there any function in oracle that supports such a sorting technique

推荐答案

知道最大深度后，您可以将该部分分为以下几个子部分:

When the maximum depth is known, you can split the section in sub-sections:

SQL> SELECT SECTION FROM DATA
  2   ORDER BY to_number(regexp_substr(SECTION, '[^.]+', 1, 1)) NULLS FIRST,
  3            to_number(regexp_substr(SECTION, '[^.]+', 1, 2)) NULLS FIRST,
  4            to_number(regexp_substr(SECTION, '[^.]+', 1, 3)) NULLS FIRST;

SECTION
-------
1
1.1
1.1.2
1.1.6
6.1
6.2
[...]
8.5
10
11

如果子节的最大深度未知(但在8位字符数据库中大概不到几百个，在ANSI字符数据库中大概不到几千个)，则可以定义一个函数来转换无法排序的数字变成可排序的字符:

If the maximum depth of sub-sections is unknown (but presumably less than a couple hundred on 8-bit character databases or less than a few thousands in ANSI-character databases), you could define a function that converts your unsortable digits into sortable characters:

SQL> CREATE OR REPLACE FUNCTION order_section (p_section VARCHAR2)
  2     RETURN VARCHAR2 IS
  3     l_result VARCHAR2(4000);
  4  BEGIN
  5     FOR i IN 1..regexp_count(p_section, '[^.]+') LOOP
  6        l_result := l_result
  7                    || CASE WHEN i > 1 THEN '.' END
  8                    || CHR(64+regexp_substr(p_section, '[^.]+', 1, i));
  9     END LOOP;
 10     RETURN l_result;
 11  END;
 12  /

Function created

SQL> SELECT SECTION, order_section(SECTION)
  2    FROM DATA
  3   ORDER BY 2;

SECTION ORDER_SECTION(SECTION)
------- -------------------------
1       A
1.1     A.A
1.1.2   A.A.B
1.1.6   A.A.F
6.1     F.A
6.2     F.B
[...]
8.5     H.E
10      J
11      K

这篇关于从Oracle对记录进行排序，带有多个小数点(.)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！