如何使用 Oracle SQL 查询首先按数字排序？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4324410/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to sort by numbers first with Oracle SQL query?
提问by rfgamaral
I have this table with a 'title' field which is varchar2 and I want to select all rows and sort them first by number and then by the alphabet as it normally happens.
我有一个带有“标题”字段的表，它是 varchar2，我想选择所有行并首先按数字然后按字母表对它们进行排序，就像通常发生的那样。
For instance, I currently get this using a simple ORDER BY titlein the end:
例如，我目前ORDER BY title最终使用一个简单的方法来获得它：
- Abc
- Def
- 321
- 美国广播公司
- 定义
- 321
But I want this:
但我想要这个：
- 321
- Abc
- Def
- 321
- 美国广播公司
- 定义
The weird thing is that SQL Developer shows the "right" order, with numbers first. But on my app (PHP using OCI8) it shows numbers last.
奇怪的是，SQL Developer 显示了“正确”的顺序，首先是数字。但是在我的应用程序（使用 OCI8 的 PHP）上，它最后显示数字。
回答by Unreason
Not an Oracle expert, but you are supposed to be able to do it without altering the session with
不是 Oracle 专家，但您应该能够在不更改会话的情况下使用
SELECT * FROM my_data SORT by NLSSORT(title,'NLS_SORT=BINARY_AI')
where you can change the NLS_SORT=to fit your needs (here are the list of values)
您可以在其中更改NLS_SORT=以满足您的需要（这里是值列表）
Keep in mind that docs says that this will force table scan, so it might be beneficial to filter them first (but if you are selecting all the table scan is what you are going to use anyway).
请记住，文档说这将强制执行表扫描，因此首先过滤它们可能是有益的（但如果您选择所有表扫描，无论如何您都将使用）。
The reason why SQL Developer exhibits different behaviour is probably because it changes the session.
SQL Developer 表现出不同行为的原因可能是因为它更改了会话。
回答by Vincent Malgrat
the difference in behaviour that you're seeing is probably because of different NLS_SORTparameter setting. Consider:
您看到的行为差异可能是因为NLS_SORT参数设置不同。考虑：
SQL> select * from nls_session_parameters where parameter='NLS_SORT';
PARAMETER VALUE
------------------------------ ----------------------------------------
NLS_SORT BINARY
SQL> SELECT * FROM my_data order by title;
TITLE
-----
321
Abc
Def
SQL> alter session set nls_sort=french;
Session altered
SQL> SELECT * FROM my_data order by title;
TITLE
-----
Abc
Def
321
You can build a query that should give you the expected result regardless of your NLS_SORTsession parameter setting, for example:
无论您的NLS_SORT会话参数设置如何，您都可以构建一个应该为您提供预期结果的查询，例如：
SQL> SELECT *
2 FROM my_data
3 ORDER BY CASE
4 WHEN regexp_like(title, '[0-9]+\.?[0-9]*') THEN
5 1
6 ELSE
7 2
8 END, title;
TITLE
-----
321
Abc
Def
