排序字母数字ID号

Question

我有一个数据库，每个事务都有一个字母数字ID。当我写我的查询并使用Order By时，它给了我一个错误的顺序。

例如：

TRN_ID      TRNDTE
000002DAAW  2020-09-12 03:45:24
000002DAAX  2020-09-12 03:45:32
000002DAAY  2020-09-12 03:45:34
000002DAAZ  2020-09-12 03:45:38
000002DAA0  2020-09-12 03:35:16
000002DAA1  2020-09-12 03:35:25
000002DAA2  2020-09-12 03:35:26
000002DAA3  2020-09-12 03:35:30
000002DAA4  2020-09-12 03:35:39
000002DAA5  2020-09-12 03:35:40
000002DAA6  2020-09-12 03:35:44
000002DAA7  2020-09-12 03:36:00
000002DAA8  2020-09-12 03:36:01
000002DAA9  2020-09-12 03:36:05
000002DABA  2020-09-12 03:48:12 <-
000002DABB  2020-09-12 03:48:15
000002DABC  2020-09-12 03:48:32
000002DABD  2020-09-12 03:48:33
000002DABE  2020-09-12 03:48:36
000002DABF  2020-09-12 03:48:46
000002DABG  2020-09-12 03:48:47
000002DABH  2020-09-12 03:48:50
000002DABI  2020-09-12 03:49:06
000002DABJ  2020-09-12 03:49:06
000002DABK  2020-09-12 03:49:09
000002DABL  2020-09-12 03:49:19
000002DABM  2020-09-12 03:49:20
000002DABN  2020-09-12 03:49:24
000002DABO  2020-09-12 03:49:33
000002DABP  2020-09-12 03:49:34
000002DABQ  2020-09-12 03:49:37
000002DABR  2020-09-12 03:49:48
000002DABS  2020-09-12 03:49:48
000002DABT  2020-09-12 03:49:51
000002DABU  2020-09-12 03:50:01
000002DABV  2020-09-12 03:50:01
000002DABW  2020-09-12 03:50:05
000002DABX  2020-09-12 03:50:15
000002DABY  2020-09-12 03:50:15
000002DABZ  2020-09-12 03:50:18
000002DAB0  2020-09-12 03:46:23 <-
000002DAB1  2020-09-12 03:46:24
000002DAB2  2020-09-12 03:46:28
000002DAB3  2020-09-12 03:47:18
000002DAB4  2020-09-12 03:47:18

正如您所看到的，查询的顺序是从A到Z，然后是0到9，但它应该先是0到9，然后是A到Z

有没有办法设置一个字符串来正确排序呢？

Answer 1

Trincot的观察(您的问题下的第二个注释)很可能是正确的:您看到的是字母在数字之前的升序(而不是相反)，因为您的排序规则需要它。

根据您的配置文件，我假设您在巴西，您的NLS_LANGUAGE参数是'BRAZILIAN PORTUGUESE'，默认的NLS_SORT参数(从NLS_LANGUAGE派生)是'WEST_EUROPEAN'。这不是Oracle的选择；巴西葡萄牙语排序规则(或者更准确地说，西欧排序规则)在字母后面有数字，而不是在它们之前。请参阅此答案末尾的演示。

所以，这就解释了这个问题。你怎么解决这个问题呢？

如果您有许多类似的查询，并且必须在所有查询中的字母前显示数字(或者如果在查询中的许多位置都有ORDER BY -例如在分析函数中，或者在match_recognize中等)，那么使用以下命令更改会话的NLS_SORT参数是有意义的

alter session set nls_sort='BINARY';

如果只需要在单个查询中执行此操作，则在ORDER BY子句中，只需使用NLSSORT函数在本地更改排序顺序(仅针对该ORDER BY子句，而不影响其他任何内容)。如下所示：

.... order by nlssort(TRN_ID, 'NLS_SORT=BINARY')

下面是一个简短的演示。

首先，在我的系统上，NLS_LANGUAGE是'AMERICAN'，默认的NLS_SORT (对于这种语言)是'BINARY'。这就是字母跟在数字后面的原因：

select col
from   (select 'A' as col from dual union all select '3' from dual)
order  by col;

COL
---
3
A

现在让我将我的NLS_LANGUAGE更改为'BRAZILIAN PORTUGUESE'。然后让我们看看这是如何(自动)影响NLS_SORT的，以及它对查询有什么影响：

alter session set nls_language='BRAZILIAN PORTUGUESE';

select parameter, value
from   v$nls_parameters
where  parameter in ('NLS_LANGUAGE', 'NLS_SORT');

PARAMETER    VALUE                 
------------ ----------------------
NLS_LANGUAGE BRAZILIAN PORTUGUESE  
NLS_SORT     WEST_EUROPEAN       



select col
from   (select 'A' as col from dual union all select '3' from dual)
order  by col;

COL
---
A
3

现在，在不更改语言的情况下，让我们将排序序列(NLS_SORT参数)更改为'BINARY' -看看查询如何生成所需的结果。我们没有改变语言，但是我们改变了整个会话的排序顺序。这将适用于当前会话中的所有其他查询。

alter session set nls_sort='BINARY';

select parameter, value
from   v$nls_parameters
where  parameter in ('NLS_LANGUAGE', 'NLS_SORT');

PARAMETER    VALUE                 
------------ ----------------------
NLS_LANGUAGE BRAZILIAN PORTUGUESE  
NLS_SORT     BINARY 




select col
from   (select 'A' as col from dual union all select '3' from dual)
order  by col;

COL
---
3
A   

最后，让我将NLS_SORT改回'WEST_EUROPEAN'，并在查询的ORDER BY子句中使用NLSSORT函数，在不接触会话参数的情况下获得相同的结果(即您需要的结果)。

alter session set nls_sort='WEST_EUROPEAN';

select col
from   (select 'A' as col from dual union all select '3' from dual)
order  by col;   -- This will produce the wrong order! See below for fix.

COL
---
A
3  

select col
from   (select 'A' as col from dual union all select '3' from dual)
order  by nlssort(col, 'nls_sort=BINARY');

COL
---
3
A