sql - 在 Oracle SQL 中对字母数字列进行排序

Question

我不是 SQL 专家。我一直在谷歌上搜索如何实现以下目标。我需要根据 ID 对我的记录进行排序。 (先是前缀，然后是它们的数值)

Table: CUSTOMER_TRANS

| ID       | Name   | Date       |
|==========|========|============|
|CP-091435 | Ola    | 01-01-2010 |
|WM-183258 | Tor    | 09-09-2001 |
|CP-109056 | Jess   | 03-03-2003 |


SELECT * FROM CUSTOMER_TRANS ORDER BY substr(ID, 4) desc;

我需要先对 2 前缀(例如 ES)进行排序，然后对数值进行排序。但是，我上面的 SQL 只返回数值最高的 WM-183258。预期的结果是首先返回“CP”前缀和最高的数值，例如以下。希望有人能给我一些启发。

预期结果:

| ID       | Name   |
|==========|========|
|CP-109056 | Ola    | 
|CP-091435 | Jess   | 
|WM-183258 | Tor    | 

Answer 1

我的 PL/SQL 很生疏，但你应该可以使用类似的东西

... ORDER BY substr(ID, 1, 2) ASC, substr(ID, 4) DESC

或如 mathguy 所指出的更好

... ORDER BY substr(ID, 1, 2) ASC, ID DESC

也就是说，按前两个字符升序排序，然后按其余降序排序。

---

从性能的角度来看，这可能不是最理想的。我会考虑将 ID 分解成它的部分，例如

ID_PREFIX CHAR(2),
ID_SUFFIX CHAR(6) -- or a numeric type, whatever is appropriate

并在两者上创建您的主键。这使得分组和排序以及显示变得容易，您可以使用

SELECT ID_PREFIX || '-' || ID_SUFFIX AS ID...