相关文章
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 sco 购买一个月的服务器，没有使用代金券可以退款吗？
1498
