Mysql数据库w/街道地址-我想按名称然后按数字排序 - Mysql DB w/Street Addresses - I want to Sort by Name then Number?

Mysql DB w/Street Addresses - I want to Sort by Name then Number?

我很难为此编写php或mysql。我有一个字段的街道地址像"12-a试驾"。。。我想先按地址名称，然后按街道编号对整个结果进行排序。我已经想好了如何按地址名排序，但无法准确地说出街道号码。

到目前为止，这是我的mysql命令：

SELECT address from property_details ORDER BY SUBSTR(LTRIM(address), LOCATE(' ',LTRIM(address)))
308 Berry Street, 
317 Berry Street, 
803 E. California, 
2046 Lost Thistle Way, 
2000 Lost Thistle Way, 
2019 Lost Thistle Way, 
14 Perry Dr, 
5 Perry Dr, 
10224 Stone Plaza, 
211 Sumney Ave, 
209 Sumney Ave, 
35-B Wicket, 
35-A Wicket, 
317-B Wicket, 
53 Wicket

当我想让它像这样显示时：

308 Berry Street, 
317 Berry Street, 
803 E. California, 
2000 Lost Thistle Way, 
2019 Lost Thistle Way, 
2046 Lost Thistle Way, 
5 Perry Dr, 
14 Perry Dr, 
10224 Stone Plaza, 
209 Sumney Ave, 
211 Sumney Ave, 
35-A Wicket, 
35-B Wicket, 
53 Wicket, 
317 Wicket

非常感谢您的帮助！

只需继续使用您已经在使用的SUBSTR和LOCATE函数。您可以通过多种方式ORDER，但如果您想要数字顺序，则需要将字符串更改为数字。这需要拉出一个连字符（如果存在）。如果你的地址是"123A Any Street"，这将不起作用。

SELECT
  address,
  CONVERT (
    IF (LOCATE('-', address),
      SUBSTR(TRIM(address), 1, LOCATE('-',TRIM(address)) - 1),
      SUBSTR(TRIM(address), 1, LOCATE(' ',TRIM(address)) - 1)
    ), UNSIGNED
  ) AS number,
  SUBSTR(TRIM(address), LOCATE(' ',TRIM(address))) AS street
FROM property_details
ORDER BY street, number;

http://sqlfiddle.com/#！9/a1775/4