1.14 排序
Oracle SQL疑难解析
1.14.1 要解决的问题
我们的文本数据混合了大写、小写字母和句子(英文句子,指第一个字母大写,后续字母小写)。我们希望把这些数据按字母顺序、大小写顺序进行排列。
1.14.2 解决方法
我们先在HR.EMPLOYEES表中创建一些混合大小写的数据。运行下面的UPDATE语句,把William Smith's的姓(last_name)改为大写:
update hr.employees
set last_name = 'SMITH'
where employee_id = 171;
下面的select语句要求按Oracle默认的排序方式显示根据last_name排序的结果:
select last_name
from hr.employees
order by last_name;
LAST_NAME
---------
…
Rogers
Russell
SMITH
Sarchand
Sciarra
Seo
Sewall
Smith
Stiles
Sullivan
…
聪明的读者可能已经猜到了排序结果。Oracle默认用二进制的排序方法。在上面这个简单的例子中,文本是按它们在代码页上对应数值的大小来排列的。编码可以是US7ASCII,WEIISO8859P1或其他编码方式。在这些代码页上,大、小写字母有不同的值,大写的值排在前面。这就是为什么上面查询结果中SMITH排在所有以“S”开头的姓的最前面。
人们通常希望看见两个“Smith”排在一起,忽略大小写。用这个NLS环境变量可以达到目的:
alter session set NLS_SORT='BINARY_CI';
select last_name
from hr.employees
order by last_name;
LAST_NAME
---------
…
Rogers
Russell
Sarchand
Sciarra
Seo
Sewall
Smith
SMITH
Stiles
Sullivan
…
1.14.3 数据库如何工作
Oracle支持区分大小写和不区分大小写两种排序方式。有个环境变量NLS_SORT进行区分大小写和不区分大小写的控制。默认的排序操作区分大小写,即NLS_SORT=BINARY。如果我们希望排序按照不区分大小写的方式,则需把环境变量NLS_SORT设置为BINARY_CI。
顾名思义,NLS_SORT仅仅影响排序结果,并不对其他大小写操作构成影响。即使把NLS_SORT设置为BINARY_CI,以不区分大小写的方式比较数据的操作仍然延续了Oracle默认的方式:
select first_name, last_name
from hr.employees
where last_name like 's%';
no rows selected
对以上结果别灰心。Oracle想到了你的需求,也提供了不区分大小写的比较方式。
比较传统的方式不是使用NLS环境变量,而是用UPPER和LOWER函数把要比较的字段名、文字都转成大写或小写后再比较。这种方法的不足之处在于,使用函数后,标准的索引就不能使用了,优化器无法工作,应对的方式是使用基于功能的索引(function-based index)。