Hackerrank THE PADS挑战：ORDER BY失效及解决方案疑问

问题背景 #

我在练习SQL，解决Hackerrank的「THE PADS」挑战，要求统计各职业人数，结果需按人数升序、职业名称字母顺序排序。

遇到的问题 #

我编写的查询语句中，子查询的ORDER BY并未生效，输出结果不符合预期：

(Select concat(name,'(',LEFT(Occupation,1),')') as name from occupations) union 
(select concat('There are a total of ', count(*),' ', lower(occupation), 's.') 
from occupations group by occupation order by count(*) asc, occupation asc)

但在测试环境中，类似的单查询写法却能正常排序：

SELECT concat(count(*), ' ',country)
from customers
group by country
ORDER BY count(*) asc

对他人解决方案的疑问 #

他人的解决方案可正常运行，但我存在两点疑问：

(Select concat(name,'(',LEFT(Occupation,1),')') as name from occupations ) union 
( select concat("There are a total of ", count(Occupation)," ",lower(Occupation),"s.") 
from Occupations group by Occupation order by count(Occupation) ) ORDER BY name

1. 删除子查询中的order by count(Occupation)后仍能正常运行，原因是什么？
2. 为何使用ORDER BY name而非order by count(*)？

问题解答 #

1. 原语句排序失效的原因 #

当使用UNION（或UNION ALL）组合多个查询结果时，只有最终的全局ORDER BY会生效，子查询内的ORDER BY不会影响最终结果的排序（除非子查询搭配LIMIT，这里并未使用）。你原语句中第二个子查询的ORDER BY仅对该子查询的临时结果排序，但UNION会重新整合所有结果，因此这个局部排序会被覆盖。

而测试环境中的语句是单个查询，没有UNION，所以ORDER BY自然能正常生效。

2. 关于他人解决方案的疑问解答 #

疑问1：删除子查询的ORDER BY仍正常运行

因为子查询的ORDER BY本来就不影响最终结果，UNION会忽略子查询的排序（除非子查询使用了LIMIT）。所以删掉它对最终结果的排序没有影响，最终排序由语句末尾的ORDER BY name控制。

疑问2：为何用ORDER BY name而非order by count(*)

最终输出分为两类行：

• 第一类是Name(X)格式的用户信息行
• 第二类是There are a total of X xxxs.格式的统计行

使用ORDER BY name的巧妙之处在于：

• 所有用户信息行的name字段是姓名加职业首字母，会按姓名的字母顺序排列
• 统计行的name字段是固定格式的字符串，首字母为'T'，会自动排在所有用户信息行之后；同时统计行的字符串结构There are a total of [数字] [职业]s.会让数字小的行排在前面（人数升序），同一人数的行则按职业名称的字母顺序排列（因为职业名称的字符串排序符合字母顺序），刚好完全匹配题目要求的排序规则。

如果直接使用order by count(*)，会因为第一类行没有count字段而报错，也无法区分两类行的排序逻辑，因此ORDER BY name是更合适的实现方式。

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