为什么Oracle SQL不允许我们在GROUP BY子句中使用列别名？ [英] Why doesn't Oracle SQL allow us to use column aliases in GROUP BY clauses?

问题描述

这是我在编写SQL查询时通常面临的一种情况。我认为在GROUP BY表达式中编写整列（例如，长整型表达式，带有长参数的求和函数）而不是别名会使查询更长，更不易读。为什么Oracle SQL不允许我们在GROUP BY子句中使用列别名？它必须有一个重要的原因。它不仅仅是Oracle SQL，实际上我相信它符合以下要求： ANSI SQL标准（尽管我没有这方面的参考）。原因在于SELECT子句在GROUP BY子句之后在逻辑上处理，因此在GROUP BY完成时，别名不存在。

也许这个有点荒谬的例子有助于澄清SQL避免的问题和含糊不清：

  SQL>从工作中选择工作作为sal，总和（sal）作为工作
 2 from scott.emp 
 3 group by job; 
 
 SAL工作
 --------- ---------- 
分析员6000 
秘书4150 
经理8275 
 PRESIDENT 5000 
 SALESMAN 5600 
  

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.

解决方案

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.

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子句中使用列别名？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！