为什么 Oracle SQL 不允许我们在 GROUP BY 子句中使用列别名？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2681494/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Why doesn't Oracle SQL allow us to use column aliases in GROUP BY clauses?
提问by Mehper C. Palavuzlar
This is a situation I'm generally facing while writing SQL queries. I think that writing the whole column (e.g. long case expressions, sum functions with long parameters) instead of aliases in GROUP BY expressions makes the query longer and less readable. Why doesn't Oracle SQL allow us to use the column aliases in GROUP BY clause? There must be an important reason behind it.
这是我在编写 SQL 查询时通常面临的情况。我认为在 GROUP BY 表达式中编写整列（例如长 case 表达式、长参数求和函数）而不是别名会使查询更长且可读性更低。为什么 Oracle SQL 不允许我们在 GROUP BY 子句中使用列别名？这背后一定有重要的原因。
回答by Tony Andrews
It isn't just Oracle SQL, in fact I believe it is conforming to the ANSI SQL standard (though I don't have a reference for that). The reason is that the SELECT clause is logically processed afterthe GROUP BY clause, so at the time the GROUP BY is done the aliases don't yet exist.
它不仅仅是 Oracle SQL，事实上我相信它符合 ANSI SQL 标准（尽管我没有参考）。原因是 SELECT 子句在 GROUP BY 子句之后进行逻辑处理，因此在 GROUP BY 完成时，别名还不存在。
Perhaps this somewhat ridiculous example helps clarify the issue and the ambiguity that SQL is avoiding:
也许这个有点荒谬的例子有助于澄清问题和 SQL 避免的歧义：
SQL> select job as sal, sum(sal) as job
2 from scott.emp
3 group by job;
SAL JOB
--------- ----------
ANALYST 6000
CLERK 4150
MANAGER 8275
PRESIDENT 5000
SALESMAN 5600
回答by keith
I know this is an old thread, but it seems the users problem wasn't really solved; the explanations were good in explaining why the group by clause doesn't let you use aliases, but no alternative was given.
我知道这是一个旧线程，但似乎用户问题并没有真正解决；解释很好地解释了为什么 group by 子句不允许您使用别名，但没有给出替代方案。
Based on the info above, the aliases can't be used in the group by since group by runs first, before aliases from the select clause are stored in memory. So the simple solution which worked for my view was to add an outer select which simply selects the aliases, and then groups at that same level.
根据上面的信息，别名不能在 group by 中使用，因为 group by 首先运行，然后 select 子句中的别名存储在内存中。因此，适用于我的视图的简单解决方案是添加一个外部选择，它只选择别名，然后在同一级别进行分组。
Example:
例子：
SELECT alias1, alias2, alias3, aliasN
FROM
(SELECT field1 as alias1, field2 as alias2, field3 as alias3, fieldN as aliasN
FROM tableName
WHERE ' ' = ' ')
GROUP BY alias1, alias2, alias3, aliasN
Pretty straight forward once you see the solution, but a PITA if trying to figure out by yourself for first time.
一旦您看到解决方案，就非常简单，但是如果第一次尝试自己解决，则需要 PITA。
This is the only way I have been able to "group by" for a derived field from a case statement, so this is a good trick to know.
这是我能够从 case 语句中为派生字段“分组”的唯一方法，所以这是一个很好的技巧。
回答by Tom Heitbrink
While it seems a logical answer, in fact it's a very user-unfriendly one. Prior to processing the query Oracle reads it, and by reading it the preprocessor can replace the alias by the original statement and still send the correct query to the database. same as you can code order by 1,2,3, you should also be able to group by 1,2,3 or alias.
虽然这似乎是一个合乎逻辑的答案，但实际上它是一个非常不友好的用户。在处理查询之前，Oracle 读取它，通过读取它，预处理器可以用原始语句替换别名，并且仍然将正确的查询发送到数据库。与您可以按 1,2,3 编码顺序相同，您还应该能够按 1,2,3 或别名进行分组。
回答by Vincent Malgrat
While I agree it would be helpful to reference expressions with aliases in the GROUP BY clause, my guess is that it is not possible because the GROUP BY clause is evaluated beforethe SELECT clause.
虽然我同意在 GROUP BY 子句中引用带有别名的表达式会有所帮助，但我猜测这是不可能的，因为 GROUP BY 子句在 SELECT 子句之前进行评估。
This would also explain why you can use column aliases in the ORDER BY clause (i-e: the ORDER BY clause is evaluated last).
这也可以解释为什么可以在 ORDER BY 子句中使用列别名（即：最后评估 ORDER BY 子句）。
回答by Michael Buen
But some RDBMS do, this works on PostgreSQL:
但是一些 RDBMS 可以，这适用于 PostgreSQL：
select emp.lastname || ' ' || emp.firstname as fullname, count(emp_work.*) as cnt
from emp
left join emp_work using(emp_id)
group by fullname
That will work, as long as the grouped alias is not the result of aggregate functions, group by cntwill not work
只要分组别名不是聚合函数的结果，这将起作用，group by cnt将不起作用
But I can hazard a guess that group by fullnamegets expanded to group by emp.lastname || ' ' || emp.firstname as fullname, and the SELECT clause just pick the fullname result from that grouping; though syntactically it looks the other way around. GROUP always executes first, then projections last(i.e. SELECT)
但是我可以冒险猜测group by fullname扩展为group by emp.lastname || ' ' || emp.firstname as fullname，而 SELECT 子句只是从该分组中选择全名结果；虽然在语法上看起来相反。GROUP 总是先执行，然后投影最后（即 SELECT）
