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

问题描述

这是我在编写 SQL 查询时通常面临的情况.我认为在 GROUP BY 表达式中编写整列(例如长 case 表达式、长参数求和函数)而不是别名会使查询更长且可读性更低.为什么 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.

推荐答案

它不仅仅是 Oracle SQL，事实上我相信它符合 ANSI SQL 标准(尽管我没有参考).原因是 SELECT 子句在 GROUP BY 子句之后进行逻辑处理，因此在 GROUP BY 完成时，别名还不存在.

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.

也许这个有点荒谬的例子有助于澄清问题和 SQL 避免的歧义:

Perhaps this somewhat ridiculous example helps clarify the issue and the ambiguity that SQL is avoiding:

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

这篇关于为什么 Oracle SQL 不允许我们在 GROUP BY 子句中使用列别名?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持跟版网！