mysql - WITH UNION ALL 无法使用 ORDER BY

Question

运行的时候

SELECT concat(name,'(',substring(occupation,1,1),')')  
FROM  occupations    
UNION ALL 
SELECT concat('There are total ',count(occupation),' ', occupation,'.') 
FROM occupations 
GROUP BY occupation 

查询工作正常，但是当我添加订单时

SELECT concat(name,'(',substring(occupation,1,1),')')  
FROM  occupations    
UNION ALL 
SELECT concat('There are total ',count(occupation),' ', occupation,'.')  
FROM occupations 
GROUP BY occupation 
ORDER BY name

这是显示错误:

Unknown column 'name' in 'order clause'

表职业字段名称，职业

Answer 1

为您的选择列添加别名:

SELECT CONCAT(name,'(', SUBSTRING(occupation,1,1),')') as name
FROM occupations
UNION ALL
SELECT CONCAT('There are total ', COUNT(occupation),' ', occupation,'.') as name
FROM occupations
GROUP BY occupation
ORDER BY name