Oracle中MIN/MAX聚合函数字符串排序结果不符预期的疑问

我无法理解Oracle使用MIN/MAX聚合函数对字符串排序的逻辑。原本认为小写字母（a、b、c）的排序优先级高于大写字母（A、B、C），因此预期MIN(name)返回"bob"（小写b优先级高于大写J），MAX(name)返回"John"。但实际查询结果相反，而PostgreSQL和SQL Server中能得到符合预期的结果。请问这是否由排序规则、语言或字符集的差异导致？

测试SQL代码 #

DROP TABLE test;
CREATE TABLE test (a INT, name VARCHAR2(10));
INSERT INTO test(a, name) 
    VALUES (1, 'John')
         , (2, 'bob')
;
COMMIT;

SELECT MIN(name) -- 返回John而非bob
     , MAX(name) -- 返回bob而非John
FROM test;

Oracle配置信息 #

用于查询配置的SQL语句：

SELECT *
FROM V$NLS_PARAMETERS;

配置信息显示：NLS_LANGUAGE为AMERICAN，NLS_TERRITORY为AMERICA，NLS_CHARACTERSET为AL32UTF8，NLS_SORT为BINARY（默认值）。

问题原因及解决方案 #

核心原因：排序规则（NLS_SORT）差异 #

Oracle默认使用BINARY排序规则，该规则基于字符的ASCII码值进行比较：

• 大写字母A-Z的ASCII码范围是65-90
• 小写字母a-z的ASCII码范围是97-122
  因此大写字母的ASCII值小于小写字母，'John'的首字母J（ASCII=74）比'bob'的首字母b（ASCII=98）更小，所以MIN(name)返回'John'，MAX(name)返回'bob'。

而PostgreSQL、SQL Server默认使用的排序规则通常基于字典序（部分规则会将小写字母排在大写前，或不区分大小写），因此会得到你预期的结果。

解决方案 #

如果需要在Oracle中实现小写字母优先的排序，可以通过以下两种方式：

1. 临时修改查询的排序规则：使用COLLATE子句指定支持小写优先的排序规则，例如：

SELECT MIN(name COLLATE "GENERIC_M_CI_AS") AS min_name
     , MAX(name COLLATE "GENERIC_M_CI_AS") AS max_name
FROM test;

2. 修改会话级或系统级NLS_SORT参数：

-- 会话级修改
ALTER SESSION SET NLS_SORT = 'GENERIC_M';

修改后再执行MIN/MAX查询，即可得到小写字母优先的结果。

内容的提问来源于stack exchange，提问作者drk