ORACLE 中的列表 [英] LISTAGG in ORACLE

问题描述

我正在尝试使用 LISTAGG() 来获取两列以上的数据.

I am trying to use LISTAGG() to fetch more than two columns.

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

但它抛出了这个错误:

: FROM keyword not found where expected
 00000 -  "FROM keyword not found where expected"
*Cause:    
*Action:
Error at Line: 3 Column: 12

谁能解释一下为什么会这样?

Can please somebody explain why it is?

推荐答案

LISTAGG 分析函数是在 Oracle 11g 第 2 版 中引入的.因此，如果您使用的是旧版本，则将无法使用它.

The LISTAGG analytic function was introduced in Oracle 11g Release 2. So, if you are on older version, you won't be able to use it.

这个错误看起来很奇怪.您实际上应该得到 ORA-00904: "DEPTNAME": invalid identifier 因为 SCOTT 架构中的标准 EMP 表没有 DEPTNAME 列.此外，您应该得到 ORA-00979: not a GROUP BY 表达式，因为您没有提到 GROUP BY 表达式中的 SELECT ed 列.

The error seems strange. You should actually get ORA-00904: "DEPTNAME": invalid identifier as the standard EMP table in SCOTT schema doesn't have DEPTNAME column. Also, you should get ORA-00979: not a GROUP BY expression as you did not mention the SELECTed columns in the GROUP BY expression.

在SCOTT架构中使用标准EMP表:

SQL> SELECT deptno,
  2    job,
  3    LISTAGG(ename, ',') WITHIN GROUP (
  4  ORDER BY ename) AS employees
  5  FROM emp
  6  GROUP BY deptno,
  7    job;

    DEPTNO JOB       EMPLOYEES
---------- --------- ------------------------
        10 CLERK     MILLER
        10 MANAGER   CLARK
        10 PRESIDENT KING
        20 CLERK     ADAMS,SMITH
        20 ANALYST   FORD,SCOTT
        20 MANAGER   JONES
        30 CLERK     JAMES
        30 MANAGER   BLAKE
        30 SALESMAN  ALLEN,MARTIN,TURNER,WARD

9 rows selected.

SQL>

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