为什么Oracle SQL不允许我们在GROUP BY子句中使用列别名？

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表达式中的别名会使查询更长，更不易读。为什么Oracle SQL不允许我们在GROUP BY子句中使用列别名？必须有一个重要原因。

5 个解决方案

#1

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 after the 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

#2

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.

根据上面的信息，在select子句中的别名存储在内存中之前，不能在组中使用别名，因为group by先运行。因此，对我的观点起作用的简单解决方案是添加一个外部选择，它只是选择别名，然后在同一级别进行分组。

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语句“分组”派生字段的唯一方法，所以这是一个很好的技巧。

#3

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 before the 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子句中使用列别名（i-e：最后评估ORDER BY子句）。

#4

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或别名进行分组。

#5

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 cnt will not work

这将起作用，只要分组的别名不是聚合函数的结果，cnt组就不起作用

But I can hazard a guess that group by fullname gets 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)

但我可以冒险猜测，fullname的组被扩展为emp.lastname ||组''|| emp.firstname作为fullname，SELECT子句只选择该分组的fullname结果;虽然在语法上它看起来相反。 GROUP总是先执行，然后是最后的投影（即SELECT）

#1