oracle的listagg函数_oracle的listagg函数

Jetbrains全系列IDE使用 1年只要46元 售后保障 童叟无欺
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 –<– WM_CONCAT ~= STRAGG


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