按SQL升序中的数值排序

I'm trying to get this output.

我想要得到这个输出。

MDT 1
MDT 2
MDT 3
MDT 11
MDT 44

but, The values are ordered alphabetically, so 123 comes before 2.

但是,这些值按字母顺序排序,所以123在2之前。

example :

MDT 1
MDT 11
MDT 156
MDT 2
MDT 3
MDT 303
MDT 44

and so on.

等等。

I'm use this code, but it seem didn't work.

我使用这段代码,但似乎无效。

SELECT * FROM file ORDER BY ABS(ID) ASC

How can I solve this?

我怎么解决这个问题?

4 个解决方案

#1

If your ID is always going to contain the prefix as MDT, then you can use this, to sort as per your requirement:

如果您的ID总是包含前缀为MDT,那么您可以使用它来根据您的要求进行排序:

SELECT * FROM File 
ORDER BY CAST(replace(ID, 'MDT ', '') AS UNSIGNED) ASC

SQLFiddle demo

#2

Try Like this it will sort based on numeric :

尝试像这样它将基于数字排序:

select substr(id,4)*1 from file order by substr(id,4)*1

It will gives

它会给出

1
2
3
11
44
...

1 2 3 11 44 ......

If You want all fields try the below query ("substr(id,4)" or "substr(id,5)") based on your string length (ex: id= proj-001911 --> take SUBSTR( id, 6 ) *1) )

如果你想要所有字段根据你的字符串长度尝试以下查询(“substr(id,4)”或“substr(id,5)”)(例如:id = proj-001911 - >取SUBSTR(id,6) )* 1))

select * from file order by substr(id,4)*1

#3

Try that snippet

试试这个片段

SELECT * FROM file ORDER BY ID + 0 ASC

#4

SELECT * FROM file
ORDER BY CONVERT(SUBSTRING(ID,5),UNSIGNED) ASC

SUBSTRING() will extract all characters after 'MDT ' and CONVERT() will change remaining substring to unsigned integer on which ORDER BY is performed

SUBSTRING()将在'MDT'之后提取所有字符,CONVERT()会将剩余的子字符串更改为执行ORDER BY的无符号整数

note SUBSTR() is a synonym for SUBSTRING().

注意SUBSTR()是SUBSTRING()的同义词。

#1