oracle的listagg函数_oracle的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 –<– 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

版权声明：本文内容由互联网用户自发贡献，该文观点仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请发送邮件至 举报，一经查实，本站将立刻删除。

发布者：全栈程序员栈长，转载请注明出处：https://javaforall.cn/196490.html原文链接：https://javaforall.cn