GROUP BY 分组查询下如何获取各分组内名称最长记录对应的ID
原语句的问题根源
你写的SELECT id, MAX(name) FROM table GROUP BY country本身不符合ANSI SQL标准规则:按country做分组聚合时,SELECT后面只能出现两类内容——分组依据列、聚合函数计算结果。你直接把没做任何聚合处理的id列放到查询列里,数据库没有统一规则判断该取分组内哪一行的id值。
部分数据库(比如未开启ONLY_FULL_GROUP_BY模式的MySQL)会兼容这种不规范写法,但返回的id是数据库临时从分组里随便捞的一个值,自然和MAX(name)所在的行对应不上。
匹配需求的正确实现
你的核心目标是取每个国家分组内,名称最长的记录对应的id,结合示例数据的预期结果(返回id=2和id=4),可以根据你使用的数据库版本选择对应写法:
方案1:主流数据库通用的窗口函数写法(适配MySQL8.0+、PostgreSQL、SQL Server、Oracle等)
这个写法逻辑最直观,不需要多层嵌套关联:
SELECT id, name, country FROM ( SELECT id, name, country, -- 按国家分区,按name长度从长到短排序,排第1的就是目标行 ROW_NUMBER() OVER (PARTITION BY country ORDER BY LENGTH(name) DESC) AS sort_rank FROM table ) ranked_table WHERE sort_rank = 1;
注意:如果你要的是按字符串字典序取最大值的name,而非长度最长,只需要把ORDER BY后面的
LENGTH(name) DESC换成name DESC,和原语句里的MAX(name)逻辑完全对齐。
如果同一个国家里有多个长度一致的最长名称,ROW_NUMBER()会随机挑一条返回;如果需要把所有匹配的行都查出来,把ROW_NUMBER()换成RANK()即可。
方案2:适配低版本数据库(不支持窗口函数,比如MySQL5.x)
先通过聚合算出每个国家的匹配基准值,再关联回原表拿对应id:
WITH country_max_config AS ( SELECT country, -- 算出每个国家的name最大长度,对应“名称最长”的筛选条件 MAX(LENGTH(name)) AS max_name_length FROM table GROUP BY country ) SELECT t.id, t.name, t.country FROM table t INNER JOIN country_max_config cmc ON t.country = cmc.country AND LENGTH(t.name) = cmc.max_name_length;
示例结果验证
结合你给出的表数据:
- CHINA分组只有Joe一条记录,对应id=2
- USA分组的三个name分别是John(4字符)、Jonah(5字符)、Jonathan(8字符),最长的是Jonathan,对应id=4
两种写法返回的结果都和预期完全匹配。
内容的提问来源于stack exchange,提问作者askingtoomuch
相关产品推荐
相关产品推荐

