MySQL使用MAX与GROUP BY查询未返回预期结果的问题求解

你当前写法的核心错误是：在非严格SQL模式下，GROUP BY查询中未加入分组字段、也没有被聚合函数包裹的列，返回值是随机的，不会和聚合函数的结果自动绑定。
你通过MAX(isPreferredName)拿到了分组内最大的首选标记值1，但查询返回的alternateName是MySQL从分组内的所有行中随机选取的，和你得到的MAX值没有关联，所以才会出现标记值正确但别名错误的情况。如果你的数据库开启了ONLY_FULL_GROUP_BY模式，这条查询会直接报错，不会返回不确定的结果。

场景1：仅需要获取首选别名 #

直接在WHERE条件中过滤isPreferredName=1即可，是性能最优的写法：

SELECT geonameid, alternateName, isPreferredName
FROM alternatename_new 
WHERE geonameid = 5128581 
  AND isoLanguage = 'en'
  AND isPreferredName = 1

场景2：兼容无首选别名的情况（MySQL 8.0+支持） #

如果存在某城市没有标记首选别名的情况，需要返回任意一个别名，可以用窗口函数按首选标记倒序排序后取第一条：

WITH ranked_alias AS (
  SELECT 
    geonameid, 
    alternateName, 
    isPreferredName,
    ROW_NUMBER() OVER (PARTITION BY geonameid ORDER BY isPreferredName DESC) AS rn
  FROM alternatename_new
  WHERE geonameid = 5128581 
    AND isoLanguage = 'en'
)
SELECT geonameid, alternateName, isPreferredName
FROM ranked_alias
WHERE rn = 1

场景3：兼容无首选别名的情况（MySQL 5.x版本） #

如果使用的是不支持窗口函数的旧版本MySQL，可以用关联子查询实现：

SELECT a.geonameid, a.alternateName, a.isPreferredName
FROM alternatename_new a
INNER JOIN (
  SELECT geonameid, MAX(isPreferredName) AS max_pref
  FROM alternatename_new
  WHERE geonameid = 5128581 
    AND isoLanguage = 'en'
  GROUP BY geonameid
) b ON a.geonameid = b.geonameid AND a.isPreferredName = b.max_pref
WHERE a.isoLanguage = 'en'

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