ORACLE 函数之listagg()使用

最新推荐文章于 2024-08-23 18:03:53 发布

data eudemon

最新推荐文章于 2024-08-23 18:03:53 发布

阅读量1.1k

点赞数 1

分类专栏： oracle-develpoer

本文链接：https://blog.csdn.net/oracle_zsq/article/details/100513678

版权

oracle-develpoer 专栏收录该内容

26 篇文章 0 订阅

订阅专栏

listagg()

1、普通函数

SQL> select listagg(ename,',')within group(order by sal)name from scott.emp;

NAME
----------------------------------------------------------------------------------------------------
SMITH,JAMES,ADAMS,MARTIN,WARD,MILLER,TURNER,ALLEN,CLARK,BLAKE,JONES,FORD,SCOTT,KING

2、分组函数

SQL> select deptno,listagg(ename,',')within group(order by sal)name from scott.emp group by deptno;

DEPTNO NAME
---------- ----------------------------------------------------------------------------------------------------
MILLER,CLARK,KING
SMITH,ADAMS,JONES,FORD,SCOTT
JAMES,MARTIN,WARD,TURNER,ALLEN,BLAKE

3、分析函数

SQL> select deptno,ename,sal,listagg(ename,',')within group(order by sal)over(partition by deptno)name from scott.emp;

DEPTNO ENAME SAL NAME
---------- ---------- ---------- ----------------------------------------
MILLER 1300 MILLER,CLARK,KING
CLARK 2450 MILLER,CLARK,KING
KING 5000 MILLER,CLARK,KING
SMITH 800 SMITH,ADAMS,JONES,SCOTT,FORD
ADAMS 1100 SMITH,ADAMS,JONES,SCOTT,FORD
JONES 2975 SMITH,ADAMS,JONES,SCOTT,FORD
SCOTT 3000 SMITH,ADAMS,JONES,SCOTT,FORD
FORD 3000 SMITH,ADAMS,JONES,SCOTT,FORD
JAMES 950 JAMES,MARTIN,WARD,TURNER,ALLEN,BLAKE
MARTIN 1250 JAMES,MARTIN,WARD,TURNER,ALLEN,BLAKE
WARD 1250 JAMES,MARTIN,WARD,TURNER,ALLEN,BLAKE
TURNER 1500 JAMES,MARTIN,WARD,TURNER,ALLEN,BLAKE
ALLEN 1600 JAMES,MARTIN,WARD,TURNER,ALLEN,BLAKE
BLAKE 2850 JAMES,MARTIN,WARD,TURNER,ALLEN,BLAKE
rows selected.