像这样的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;
产量
B002AACD0A 000000000000 978123456789
取消注释WHERE限制后,结果是
B002AACD0A 978123456789
我原本期望结果只是978123456789,因为在没有限制的情况下运行查询时,在000000000000之前返回B002AACD0A.
如何解释这种行为？我应该如何排序和比较varchars,以便它们可以像我可以用整数一样工作？
编辑：有趣的是,当将限制更改为x>’B002AACD0A’时,结果为空.将其更改为tox> 978123456789将返回B002AACD0A.
即比较时：
B002AACD0A > 978123456789 > 000000000000
但在排序时
978123456789 > 000000000000 > B002AACD0A
编辑2：当明确地使用二进制排序时(按NLSSORT(x,’NLS_SORT = BINARY_AI’)排序),结果是B002AACD0A> 978123456789> 000000000000并且匹配比较的行为.但我仍然不知道为什么会发生这种情况.
彼得,
排序的行为由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
