23c 新特性之基于别名的GROUP BY
一 描述
1.1 基于别名的GROUP BY的介绍
从Oracle Database 23c开始,Oracle数据库支持基于别名的GROUP BY操作,相较于之前的数据库版本经常遇到在GROUP BY 后面不能跟字段别名的情况,如果是一个比较复杂的表达式,在GROUP BY 后面输入,不太方便,很多时候,认为ORDER BY 后面都可以跟字段别名,或字段顺序号,那GROUP BY 后面也可以,但是实际上是不支持的。因此在23c推出以基于表达式的别名或者它在 SELECT 列表中的位置指定 GROUP BY 和 HAVING 操作,从而简化了SQL写法。
二 基于别名的GROUP BY测试
2.1 创建测试数据
SQL> create table employees(department_id int, salary number(10));
SQL> insert into employees values (1,1000);
SQL> insert into employees values (2,2000);
SQL> insert into employees values (3,3000);
SQL> insert into employees values (4,4000);
2.2 19c环境测试
SQL> select banner from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
SQL> select department_id deptid ,sum(salary) from employees group by deptid having deptid > 3;
select department_id deptid ,sum(salary) from employees group by deptid having deptid > 3
*
ERROR at line 1:
ORA-00904: "DEPTID": invalid identifier
SQL>
这里报错了,在GROUP BY 后面需要实际的字段名
2.3 23c环境测试
SQL> select BANNER from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 23c Free, Release 23.0.0.0.0 - Developer-Release
SQL> select department_id deptid,sum(salary)
2 from employees
3 group by deptid having deptid > 3;