MySQL中GROUP BY与ORDER BY联用报错问题咨询（HackerRank The PADS题目）

嗨，我来帮你理清楚这个问题哈！咱们先把你的问题场景和代码明确下来：

问题情况梳理 #

你在做HackerRank的「The PADS」题目时，写出了两个查询语句，第一个触发了错误，第二个却能正常运行：

报错的Query 1

SELECT CONCAT( "There are a total of " ," ", COUNT(OCCUPATION) ," ", LOWER(OCCUPATION) , "s." ) AS ENT
FROM OCCUPATIONS 
GROUP BY OCCUPATION 
ORDER BY COUNT(OCCUPATION),NAME;

触发的错误信息

ERROR 1055 (42000) at line 5: Expression #2 of ORDER BY clause is not in GROUP BY clause and contains nonaggregated column 'OCCUPATIONS.Name' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

正常运行的Query 2

SELECT CONCAT( "There are a total of " ," ", COUNT(OCCUPATION) ," ", LOWER(OCCUPATION) , "s." ) AS ENT
FROM OCCUPATIONS 
GROUP BY OCCUPATION 
ORDER BY ENT;

错误原因详解 #

这个报错的核心是MySQL默认开启的sql_mode=only_full_group_by严格分组模式在起作用，它有个硬性要求：

• 当使用GROUP BY分组后，SELECT、ORDER BY中用到的列，要么是GROUP BY子句里明确列出的分组列，要么是经过聚合函数（比如COUNT()、MAX()）处理后的结果。

回到你的Query 1，ORDER BY里的NAME列既不在GROUP BY OCCUPATION的分组列中，也没有被聚合函数处理——按职业分组后，每个职业组里会对应多个用户的NAME，数据库根本不知道你要拿哪个NAME来做排序依据，所以直接抛出了错误。

为什么Query 2能正常运行？ #

Query 2里用ORDER BY ENT，而ENT是你SELECT子句生成的别名，它的计算逻辑完全基于分组后的聚合结果COUNT(OCCUPATION)和分组列OCCUPATION，完全符合only_full_group_by的规则，数据库能清晰识别排序依据，所以可以正常执行。

实现你预期排序逻辑的正确写法 #

看你提到“我 was expecting to sort the query first...”，应该是想先按职业人数排序，再按职业名称排序吧？那只需要把ORDER BY里的NAME换成OCCUPATION即可（OCCUPATION是分组列，符合规则）：

SELECT CONCAT( "There are a total of " ," ", COUNT(OCCUPATION) ," ", LOWER(OCCUPATION) , "s." ) AS ENT
FROM OCCUPATIONS 
GROUP BY OCCUPATION 
ORDER BY COUNT(OCCUPATION), OCCUPATION;

备注：内容来源于stack exchange，提问作者drrracula