一条 SQL 语句如:
select * from (
select '000000000000' as x from dual
union
select '978123456789' as x from dual
union
select 'B002AACD0A' as x from dual
) /*where x>'000000000000'*/ order by x;
Yields:
B002AACD0A
000000000000
978123456789
取消注释 WHERE 限制后,结果是:
B002AACD0A
978123456789
我原以为结果只是978123456789
since B002AACD0A
之前返回000000000000
运行查询时不受限制。
如何解释这种行为?我该如何对 varchar 进行排序和比较,以便它们可以像处理整数一样一起工作?
有趣的是,当将限制更改为x>'B002AACD0A'
,结果为空。将其更改为x>978123456789
回报B002AACD0A
.
IE。比较时:
B002AACD0A > 978123456789 > 000000000000
但排序时:
978123456789 > 000000000000 > B002AACD0A
当显式使用二元排序时(order by NLSSORT(x,'NLS_SORT=BINARY_AI')
),结果是B002AACD0A>978123456789>000000000000
并匹配比较的行为。但我仍然不知道为什么会发生这种情况。
Peter,
排序的行为由NLS_SORT会话参数,而比较的行为取决于NLS_COMP范围。你一定有不匹配的地方。
我使用以下参数获得与您相同的结果:
SQL> SELECT *
2 FROM nls_session_parameters
3 WHERE parameter IN ('NLS_COMP', 'NLS_SORT');
PARAMETER VALUE
------------------------------ ----------------------------------------
NLS_SORT FRENCH
NLS_COMP BINARY
然而,当两者匹配时,结果是一致的:
SQL> alter session set nls_comp=LINGUISTIC;
Session altered
SQL> select * from (
2 select '000000000000' as x from dual
3 union
4 select '978123456789' as x from dual
5 union
6 select 'B002AACD0A' as x from dual
7 ) /*where x>'000000000000'*/ order by x;
X
------------
B002AACD0A
000000000000
978123456789
SQL> select * from (
2 select '000000000000' as x from dual
3 union
4 select '978123456789' as x from dual
5 union
6 select 'B002AACD0A' as x from dual
7 ) where x > '000000000000' order by x;
X
------------
978123456789
本文内容由网友自发贡献,版权归原作者所有,本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容,请联系:hwhale#tublm.com(使用前将#替换为@)