Oracle11.2新特性之listagg函式

Oracle11.2新增了LISTAGG函式，可以用於字串聚集，測試如下：

1,版本
SQL> select * from v$version;
 
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
PL/SQL Release 11.2.0.1.0 - Production
CORE 11.2.0.1.0 Production
TNS for Linux: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production

 
2，測試資料
SQL>
SQL> select empno,ename,deptno from scott.emp;
 
EMPNO ENAME      DEPTNO
----- ---------- ------
 7369 SMITH          20
 7499 ALLEN          30
 7521 WARD           30
 7566 JONES          20
 7654 MARTIN         30
 7698 BLAKE          30
 7782 CLARK          10
 7788 SCOTT          20
 7839 KING           10
 7844 TURNER         30
 7876 ADAMS          20
 7900 JAMES          30
 7902 FORD           20
 7934 MILLER         10
 
14 rows selected
 
3，作為聚集函式
SQL> SELECT deptno,
  2         LISTAGG(ename, ',') WITHIN GROUP(ORDER BY ename) AS employees
  3    FROM scott.emp
  4   GROUP BY deptno;
 
DEPTNO EMPLOYEES
------ --------------------------------------------------------------------------------
    10 CLARK,KING,MILLER
    20 ADAMS,FORD,JONES,SCOTT,SMITH
    30 ALLEN,BLAKE,JAMES,MARTIN,TURNER,WARD
 
SQL>
--更換排序列
SQL> SELECT deptno,
  2         LISTAGG(ename, ',') WITHIN GROUP(ORDER BY hiredate) AS employees
  3    FROM scott.emp
  4   GROUP BY deptno;
 
DEPTNO EMPLOYEES
------ --------------------------------------------------------------------------------
    10 CLARK,KING,MILLER
    20 SMITH,JONES,FORD,SCOTT,ADAMS
    30 ALLEN,WARD,BLAKE,TURNER,MARTIN,JAMES

--order by必須存在
SQL> SELECT deptno,
  2         LISTAGG(ename, ',') WITHIN GROUP() AS employees
  3    FROM scott.emp
  4   GROUP BY deptno;
 
SELECT deptno,
       LISTAGG(ename, ',') WITHIN GROUP() AS employees
  FROM scott.emp
 GROUP BY deptno
 
ORA-30491: ORDER BY 子句缺失

SQL> SELECT deptno,
  2         LISTAGG(ename, ',') WITHIN GROUP(order by null) AS employees
  3    FROM scott.emp
  4   GROUP BY deptno;
 
DEPTNO EMPLOYEES
------ --------------------------------------------------------------------------------
    10 CLARK,KING,MILLER
    20 ADAMS,FORD,JONES,SCOTT,SMITH
    30 ALLEN,BLAKE,JAMES,MARTIN,TURNER,WARD
==〉按字母順序排列

4，LISTAGG作為分析函式使用
SQL> SELECT empno,
  2         ename,
  3         deptno,
  4         LISTAGG(ename, ',') WITHIN GROUP(ORDER BY ename) over(partition by deptno) AS employees
  5    FROM scott.emp;
 
EMPNO ENAME      DEPTNO EMPLOYEES
----- ---------- ------ --------------------------------------------------------------------------------
 7782 CLARK          10 CLARK,KING,MILLER
 7839 KING           10 CLARK,KING,MILLER
 7934 MILLER         10 CLARK,KING,MILLER
 7876 ADAMS          20 ADAMS,FORD,JONES,SCOTT,SMITH
 7902 FORD           20 ADAMS,FORD,JONES,SCOTT,SMITH
 7566 JONES          20 ADAMS,FORD,JONES,SCOTT,SMITH
 7788 SCOTT          20 ADAMS,FORD,JONES,SCOTT,SMITH
 7369 SMITH          20 ADAMS,FORD,JONES,SCOTT,SMITH
 7499 ALLEN          30 ALLEN,BLAKE,JAMES,MARTIN,TURNER,WARD
 7698 BLAKE          30 ALLEN,BLAKE,JAMES,MARTIN,TURNER,WARD
 7900 JAMES          30 ALLEN,BLAKE,JAMES,MARTIN,TURNER,WARD
 7654 MARTIN         30 ALLEN,BLAKE,JAMES,MARTIN,TURNER,WARD
 7844 TURNER         30 ALLEN,BLAKE,JAMES,MARTIN,TURNER,WARD
 7521 WARD           30 ALLEN,BLAKE,JAMES,MARTIN,TURNER,WARD
 
14 rows selected

5,其他實現方法參考
--model
SQL> SELECT deptno, vals
  2    FROM (SELECT deptno, RTRIM(vals, ',') AS vals, rn
  3            FROM scott.emp MODEL PARTITION BY(deptno) DIMENSION BY(ROW_NUMBER() OVER(PARTITION BY deptno ORDER BY ename) AS rn) MEASURES(CAST(ename AS VARCHAR2(4000)) AS vals) RULES(vals [ ANY ] ORDER BY rn DESC = vals [ CV() ] || ',' || vals [ CV() + 1 ]))
  4   WHERE rn = 1
  5   ORDER BY deptno;
 
    DEPTNO VALS
---------- --------------------------------------------------------------------------------
        10 CLARK,KING,MILLER
        20 ADAMS,FORD,JONES,SCOTT,SMITH
        30 ALLEN,BLAKE,JAMES,MARTIN,TURNER,WARD
 

--表函式：WMSYS.WM_CONCAT，10G已經提供該函式
SQL>
SQL> SELECT deptno, WMSYS.WM_CONCAT(ename) AS vals --  2    FROM scott.emp
  3   GROUP BY deptno;
 
DEPTNO VALS
------ --------------------------------------------------------------------------------
    10 CLARK,MILLER,KING
    20 SMITH,FORD,ADAMS,SCOTT,JONES
    30 ALLEN,JAMES,TURNER,BLAKE,MARTIN,WARD