按字母顺序排序MySQL结果，但最后使用数字

Often, sorting is done with symbols sorted to the top, like 0 or * or &. This is the default way that mysql sorts; numbers and symbols and then A-Z. However, that makes the often ugliest or most badly formatted results float to the top (e.g. a result of @#$@3423 or 8 inch or &amp).

通常,排序是使用排序到顶部的符号完成的,如0或*或&。这是mysql排序的默认方式;数字和符号,然后是A-Z。然而,这使得通常最丑或格式最差的结果浮动到顶部(例如@#$ @ 3423或8英寸或&的结果)。

So I'd like to do a modified form of that, letters first A-Z, and then special characters last.

所以我想做一个修改过的形式,首先是字母A-Z,然后是特殊字符。

How would I go about creating that type of sort? Something in the ORDER BY clause?

我将如何创建这种类型的排序? ORDER BY子句中有什么东西?

3 个解决方案

#1

Based on a google-cached link to this page: http://www.google.com/url?sa=t&source=web&cd=3&ved=0CCUQFjAC&url=http%3A%2F%2Fblog.feedmarker.com%2F2006%2F02%2F01%2Fhow-to-do-natural-alpha-numeric-sort-in-mysql%2F&ei=Zg2_TZyKDaffiALjjqwo&usg=AFQjCNGS-rX7AmfrumXK8J7bVSj96bSSmQ

基于此页面的google-cached链接:http://www.google.com/url?sa = t&source = web&cd = 3&ed = 0CCUQFjAC&url = http%3A %% 2F%2Fblog.feedmarker.com%2F2006%2F02%2F01 %2Fhow到DO-自然字母数字排序合的MySQL%2F&EI = Zg2_TZyKDaffiALjjqwo&USG = AFQjCNGS-rX7AmfrumXK8J7bVSj96bSSmQ

EDIT: Original link is dead. Here is another link which actually explains what is happening better than the first link did:

编辑:原始链接已死。这是另一个链接,它实际上解释了比第一个链接更好的事情:

http://matthewturland.com/2008/11/05/natural-ordering-in-mysql/

You might try this

你可以试试这个

SELECT names FROM your_table ORDER BY names + 0 ASC

#2

Select ...
From ...
Order By Case When Col Like '[0-9]%' Then 1 Else 0 End Asc
    , Col

Another solution that would account for special characters:

另一种解决特殊字符的解决方案:

Select ...
From ...
Order By Case When Col Like '[A-Z]%' Then 0 Else 1 End Asc
    , Col

#3

This works for me:

这对我有用:

SELECT * FROM `yourTable` ORDER BY `yourDatabase`.`yourColumn`  ASC

#1