主页 / dba / 问题 / 331880
Accepted
Andy DB Analyst
Asked: 2023-10-05 22:43:42 +0800 CST

Oracle MAX 给出的结果与 ORDER BY desc 不同

  • 772

当尝试对一些十六进制值进行排序时，我得到了 MAX() 聚合和 ORDER BY desc 排序的不同结果：

with some_data as
(
    select '123A55' as hex
    from dual
    union all
    select '123255' as hex
    from dual
)

select max(hex)
from some_data
;

Result: 123A55

with some_data as
(
    select '123A55' as hex
    from dual
    union all
    select '123255' as hex
    from dual
)

select *
from some_data
order by hex desc
;

Result:
123255
123A55

在 MySQL DB 中，这两种情况都给出 123A55。

ORACLE 中 MAX() MIN() 和 ORDER BY 的排序顺序是否有不同的规则？

oracle

1 个回答

  1. Best Answer

    结果取决于您的环境设置。例子：

    with some_data as
( select '123A55' as hex from dual union all
  select '123255' as hex from dual)
select * from some_data order by hex desc;

HEX   
------
123A55
123255

    但：

    alter session set NLS_SORT='GERMAN';

with some_data as
( select '123A55' as hex from dual union all
  select '123255' as hex from dual)
select * from some_data order by hex desc;

HEX   
------
123255
123A55

    在这种情况下，MAX 不受上述设置的影响 - 同一会话：

    with some_data as
( select '123A55' as hex from dual union all
  select '123255' as hex from dual)
select max(hex) from some_data;

MAX(HE
------
123A55

    但在同一会话中设置另一个值：

    alter session set NLS_COMP = 'LINGUISTIC';

with some_data as
( select '123A55' as hex from dual union all
  select '123255' as hex from dual)
select max(hex) from some_data;

MAX(HE
------
123255

    即使在语句级别也可以指定上述设置。

    with some_data as
( select '123A55' as hex from dual union all
  select '123255' as hex from dual)
select * from some_data order by NLSSORT(hex, 'NLS_SORT = BINARY') desc;

HEX   
------
123A55
123255

with some_data as
( select '123A55' as hex from dual union all
  select '123255' as hex from dual)
select * from some_data order by NLSSORT(hex, 'NLS_SORT = GERMAN') desc;

HEX   
------
123255
123A55
    • 2

相关问题