Oracle/MYSQL:对包含字母数字值的列的select查询中的记录进行排序

Question

我知道这项质询是以不同的形式提出的，但我的要求却有少许不同。

假设我有一个包含如下数据的表：

ID  NAME        VALUE

-----------------------------

1   ABC-2-2     X
2   PQRS-1-3    Y
3   ABC-3-2     Z
4   PQRS-1-4    A
5   PQRS-3-4    B
6   MNO-2-1     C
7   AAA-1       D
8   BBB-2       E
9   CCC-3       F

现在，我期望的输出应该如下所示：

ID  NAME        VALUE

-----------------------------
7   AAA-1       D
2   PQRS-1-3    Y
4   PQRS-1-4    A
8   BBB-2       E
6   MNO-2-1     C
1   ABC-2-2     X
9   CCC-3       F
3   ABC-3-2     Z
5   PQRS-3-4    B

请注意，这不是直接的字母数字排序。相反，忽略第一个"-“之前的值，并根据名称中第一个"-”之后的内容对字段进行排序。

我对PL/SQL不是很熟悉，在这方面有任何帮助都将不胜感激。

谢谢。

PS:请注意，这应该在Oracle和MySQL上都有效。

Answer 1

对于您的示例，这就足够了(Oracle语法)：

ORDER BY SUBSTR(name,4)

如果第一个连字符之前的字符数可以改变，您可以这样做(同样是Oracle语法)：

ORDER BY SUBSTR(name,INSTR(name,'-')+1)

然而，如果你有这样的代码，那就不能工作了：

AAA-10-1
AAA-8-1
AAA-9-1

并期望在AAA-9-1之后出现AAA-10-1。然后，您需要进一步解析它：

ORDER BY LPAD(SUBSTR(name,INSTR(name,'-')+1, INSTR(name,'-',1,2)-INSTR(name,'-')-1),10,'0'),
         LPAD(SUBSTR(name,INSTR(name,'-',1,2)+1),10,'0')

(注意:我已经使用LPAD(x,10，'0000000001‘)将类似'1’的值转换为‘0000000001’，而不是使用TO_NUMBER，因为如果数据中有任何非数字，这可能会失败。)

示例：

with data as
(
select 'AAA-1' name from dual
union all
select 'PQR-1-4' name from dual
union all
select 'PQR-1-3' name from dual
union all
select 'AAA-10-10' name from dual
union all
select 'AAA-10-1' name from dual
union all
select 'AAA-9-10' name from dual
union all
select 'AAA-9-1' name from dual
)
select *
from data
ORDER BY LPAD(SUBSTR(name,INSTR(name,'-')+1, INSTR(name,'-',1,2)-INSTR(name,'-')-1),10,'0'),
         LPAD(SUBSTR(name,INSTR(name,'-',1,2)+1),10,'0');

输出：

NAME
---------
PQR-1-3
PQR-1-4
AAA-9-1
AAA-9-10
AAA-10-1
AAA-10-10
AAA-1

如果AAA-1应该在第一位：

ORDER BY LPAD(SUBSTR(name,INSTR(name,'-')+1, INSTR(name||'-','-',1,2)-INSTR(name,'-')-1),10,'0'),
         LPAD(SUBSTR(name,INSTR(name||'-','-',1,2)+1),10,'0') nulls first

Answer 2

不确定mysql语法，但您可以在oracle中执行此操作：

select * from  <your_table>
order by substr(name, 5)

Answer 3

在mssql中，查找问题的语法是：

select * from mytable order by substring(name,PATINDEX('%-%',name)+1,len(name)-PATINDEX('%-%',name))

SqlFiddle