本文介绍了WITH UNION ALL无法使用ORDER BY的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!

问题描述

我跑步时

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

查询工作正常,但是当我通过

the query is working fine but when I add order by

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

这显示了一个错误:

表职业字段名称,职业

推荐答案

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

更新:为何如此:
因为您没有名为name的公共列.当您将函数应用于查询时,mysql会使用该函数名和参数的组合为您创建一个列名.实际上,这意味着查询的名称将为concat(name,'(',substring(occupation,1,1),')')(很可能是大写),而第二个名称将为concat('There are total ',count(occupation),' ', occupation,'.')

Update: Why this is so:
Because you don't have a common column named, name. When you apply a function to a query, mysql use a combination of that function name and parameters to create a column name for you. Which in reality means query will have a name of concat(name,'(',substring(occupation,1,1),')') (most probably upper cased) while the second will have concat('There are total ',count(occupation),' ', occupation,'.')

就Union而言,名称没有什么不同,因为mysql足够聪明,可以按数据类型将它们组合在一起.但是,当您添加order by子句时,您需要两个列都通用的列名.

It doesn't matter that the names are different as far as Union is concerned because mysql is smart enough to combine them together by their data type. However when you add an order by clause you need a column name that's common to both.

这篇关于WITH UNION ALL无法使用ORDER BY的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!

08-26 07:43
查看更多