MySQL LEFT JOIN结合多排序规则实现多语言城市名称优先匹配

业务需求 #

需要关联两张表查询符合条件的城市列表：

• geoname 表：存储城市ID、国家编码、功能编码
• alternatename 表：存储每个城市ID对应的多语言翻译名称

名称匹配优先级规则：

1. 语言优先级：优先取意大利语（lang='it'）版本，不存在则取英语（lang='en'）版本，前两者都不存在则取lang为NULL的默认版本
2. 同语言下优先级：优先取isPreferred标记为1的首选名称

最终返回结果按名称升序排列。

原查询存在的问题 #

原查询未按优先级规则匹配名称，返回结果不符合预期，查询语句如下：

SELECT g.geonameid, name 
FROM geoname g 
LEFT JOIN alternatename a ON g.geonameid = a.geonameid AND 
  (a.lang = 'it' OR a.lang = 'en' OR a.lang = NULL) 
WHERE name IS NOT NULL and fcode="PPL"
GROUP BY g.geonameid 
ORDER BY isPreferred DESC, name ASC 
LIMIT 0,20

修正后实现方案 #

步骤1：查询符合筛选条件的城市ID #

先筛选功能编码为PPL的城市ID：

SELECT 
geonameid
FROM geoname
WHERE fcode='PPL'
LIMIT 0,10

输出结果为：12345、78910、34542

步骤2：按优先级匹配城市名称 #

使用COALESCE函数按优先级顺序取第一个非空的名称，结合条件聚合实现优先级匹配：

SELECT 
geonameid, 
COALESCE( 
MAX(CASE WHEN a.lang = 'it' and isPreferred=1 THEN name END), 
MAX(CASE WHEN a.lang = 'it' THEN name END), 
MAX(CASE WHEN a.lang = 'en' and isPreferred=1 THEN name END),  
MAX(CASE WHEN a.lang = 'en' THEN name END), 
MAX(name) ) as name 
FROM alternatename a 
WHERE geonameid IN (12345,78910,34542) 
GROUP BY geonameid 
ORDER BY name ASC 

执行后即可得到符合预期的结果：

内容的提问来源于stack exchange，提问作者Luca