listagg

最新推荐文章于 2024-05-16 09:27:34 发布

原创最新推荐文章于 2024-05-16 09:27:34 发布 · 461 阅读

0 ·

CC 4.0 BY-SA版权

oracle 专栏收录该内容

50 篇文章

订阅专栏

本文通过多个示例介绍了SQL中LISTAGG函数的基本用法，包括如何使用该函数进行分组聚合操作，以及如何结合OVER子句实现分区内的字符串连接。

语法:
listagg(column_name[,split_character]) within group(order by column_name,...) [over(partition by column_name,...)]

SQL> select listagg(ename) within group(order by rowid) as ename from emp ;
ENAME
--------------------------------------------------------------------------------
SMITHALLENWARDJONESMARTINBLAKECLARKSCOTTKINGTURNERADAMSJAMESFORDMILLER

SQL> select listagg(ename,',') within group(order by rowid) as ename from emp ;
ENAME
--------------------------------------------------------------------------------
SMITH,ALLEN,WARD,JONES,MARTIN,BLAKE,CLARK,SCOTT,KING,TURNER,ADAMS,JAMES,FORD,MIL

SQL> select deptno,listagg(ename,',') within group(order by rowid) as ename from emp
2 group by deptno ;
DEPTNO ENAME
------ --------------------------------------------------------------------------------
10 CLARK,KING,MILLER
20 SMITH,JONES,SCOTT,ADAMS,FORD
30 ALLEN,WARD,MARTIN,BLAKE,TURNER,JAMES

SQL> select deptno,ename,listagg(ename,',') within group(order by rowid) over(partition by deptno) as ename from emp
2 /
DEPTNO ENAME ENAME
------ ---------- --------------------------------------------------------------------------------
10 CLARK CLARK,KING,MILLER
10 KING CLARK,KING,MILLER
10 MILLER CLARK,KING,MILLER
20 SMITH SMITH,JONES,SCOTT,ADAMS,FORD
20 JONES SMITH,JONES,SCOTT,ADAMS,FORD
20 SCOTT SMITH,JONES,SCOTT,ADAMS,FORD
20 ADAMS SMITH,JONES,SCOTT,ADAMS,FORD
20 FORD SMITH,JONES,SCOTT,ADAMS,FORD
30 ALLEN ALLEN,WARD,MARTIN,BLAKE,TURNER,JAMES
30 WARD ALLEN,WARD,MARTIN,BLAKE,TURNER,JAMES
30 MARTIN ALLEN,WARD,MARTIN,BLAKE,TURNER,JAMES
30 BLAKE ALLEN,WARD,MARTIN,BLAKE,TURNER,JAMES
30 TURNER ALLEN,WARD,MARTIN,BLAKE,TURNER,JAMES
30 JAMES ALLEN,WARD,MARTIN,BLAKE,TURNER,JAMES
14 rows selected

SQL>