甲骨文sql listagg [英] oracle sql listagg

问题描述

SELECT deptno, 
       LISTAGG(ename, ',') WITHIN GROUP (ORDER BY ename) AS employees
  FROM emp
 GROUP BY deptno;

错误:-ORA-00923:在预期位置找不到FROM关键字 00923.00000-未在预期的位置找到FROM关键字" *原因:
*操作:第1行的错误:42列

Error:- ORA-00923: FROM keyword not found where expected 00923. 00000 - "FROM keyword not found where expected" *Cause:
*Action: Error at Line: 1 Column: 42

Oracle数据库11g企业版11.1.0.7.0版-64位生产

Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production

推荐答案

对于Oracle的10gR2或11gR1版本，可以在sys_connect_by_path的作用下使用层次查询:

For the 10gR2 or 11gR1 versions of Oracle , you can use hierarchical queries with the contribution of sys_connect_by_path :

with emp( ename, deptno ) as
(
 select 'CLARK',10 from dual union all 
 select 'MILLER',10 from dual union all
 select 'KING',10 from dual union all
 select 'FORD',20 from dual union all
 select 'SCOTT',20 from dual union all
 select 'JONES',20 from dual union all
 select 'SMITH',20 from dual union all
 select 'ADAMS',20 from dual union all
 select 'WARD',30 from dual union all
 select 'MARTIN',30 from dual union all
 select 'TURNER',30 from dual union all
 select 'JAMES',30 from dual union all
 select 'ALLEN',30 from dual union all
 select 'BLAKE',30 from dual
)
select deptno, ltrim(sys_connect_by_path(ename, ','), ',') as enames
  from (select deptno,
               ename,
               row_number() over(partition by deptno order by ename) as rn
          from emp)
 where connect_by_isleaf = 1
connect by deptno = prior deptno
       and rn = prior rn + 1
 start with rn = 1;

DEPTNO  ENAMES
------  ------------------------------------
  10    CLARK,KING,MILLER
  20    ADAMS,FORD,JONES,SCOTT,SMITH
  30    ALLEN,BLAKE,JAMES,MARTIN,TURNER,WARD

这篇关于甲骨文sql listagg的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！