如何首先使用Oracle SQL查询按数字排序? [英] How to sort by numbers first with Oracle SQL query?
问题描述
我在这张表上有一个'title'字段，该字段是varchar2，我想选择所有行，然后按通常的顺序先按数字然后按字母对它们进行排序.
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.
例如，我目前最后使用一个简单的ORDER BY title来获取它:
For instance, I currently get this using a simple ORDER BY title in the end:
- Abc
- Def
- 321
但是我想要这个:
- 321
- Abc
- Def
奇怪的是，SQL Developer显示正确"的顺序，以数字开头.但是在我的应用程序(使用OCI8的PHP)上，它最后显示数字.
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.
推荐答案
不是Oracle专家，但是您应该能够在不更改会话的情况下使用
Not an Oracle expert, but you are supposed to be able to do it without altering the session with
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)
请记住，文档说这将强制进行表扫描，因此首先过滤它们可能会有所益处(但是如果您选择所有表扫描，无论如何都将要使用它).
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).
SQL Developer表现出不同行为的原因可能是因为它更改了会话.
The reason why SQL Developer exhibits different behaviour is probably because it changes the session.
这篇关于如何首先使用Oracle SQL查询按数字排序?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
