为什么 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
相关文章