在没有定义排序规则的情况下对MySQL字符串进行排序 - Ordering MySQL string results without a defined Collation?

Ordering MySQL string results without a defined Collation?

假设我得到以下结果：

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

似乎不能同时按整数和字符串排序，但可以根据是否存在"Airport"来派生要排序的字符串。这很傻，但它有效：

ORDER BY IF(name NOT LIKE '% Airport', CONCAT(name, ' Birport'), name)

下面的查询将为您提供预期的输出（即使查询很复杂）。

SELECT t.name
FROM (
SELECT @num := IF(@num IS NULL, 1, @num+1) AS number, name
FROM places
ORDER BY IF ((name LIKE '% Airport'), SUBSTRING(name, 1, LOCATE(' Airport', name) - 1), name)     DESC) t
ORDER BY t.number DESC;

这是一个例子。

http://www.sqlfiddle.com/#！2/117b7/80