sql - oracle中不区分大小写的排序

Question

我想按以下顺序订购以下文本，但是在尝试以下查询后它不起作用。

要订购的值是“A”、“B”、“Y”、“Z”、“a”、“b”、“y”、“z”。

预期结果“ZzYyBbAa”

SELECT COL FROM TABLE ORDER BY COL DESC;
SELECT COL FROM TABLE ORDER BY UPPER/LOWER(COL) DESC; Result-> ZzYybBaA
SELECT COL FROM TABLE ORDER BY NLS_UPPER/NLS_LOWER(COL) DESC; Result-> ZzYybBaA

Answer 1

首先，您可以按列的大写(或小写)大小写进行排序，但是一旦完成，您就需要按文本本身排序以获取首字母的顺序；例如:

with sample_data as (select 'A' txt from dual union all
                     select 'B' txt from dual union all
                     select 'Y' txt from dual union all
                     select 'Z' txt from dual union all
                     select 'a' txt from dual union all
                     select 'b' txt from dual union all
                     select 'y' txt from dual union all
                     select 'z' txt from dual)
select txt
from   sample_data
order by upper(txt) desc, txt;


TXT
---
Z  
z  
Y  
y  
B  
b  
A  
a