php - 在没有定义排序规则的情况下排序MySQL字符串结果？

说我有以下结果:

Mackay
Mackay Airport
Melbourne
Melbourne Airport
Sydney
Sydney Ac
Sydney Airport

我怎样才能做到，所以总是在机场的顶部订购它们？按字母顺序，例如:

Mackay Airport
Mackay
Melbourne Airport
Melbourne
Sydney Airport
Sydney
Sydney Ac

在如何使机场更加突出方面有些困惑。

最佳答案

不知道以下查询是否涵盖所有情况，但似乎可以处理您的示例数据:

select name,
SUBSTRING_INDEX(name,'Airport',1)
as l,
LOCATE('Airport',name) as r from
(
select 'Sydney Airport' as name
union all
select 'Sydney'
union all
select 'Sydney Ac'
union all
select '
Mackay Airport'
union all
select 'Mackay'
union all
select 'Melbourne'
union all
select 'Melbourne Airport'
)a
order by l asc, r desc

与表，它看起来像

select name
from table1
ORDER BY SUBSTRING_INDEX(name,'Airport',1) ASC,
LOCATE('Airport',name) DESC