oracle之listagg()函数

最新推荐文章于 2025-11-13 09:52:16 发布

原创最新推荐文章于 2025-11-13 09:52:16 发布 · 置顶 · 1.0k 阅读

3 ·

CC 4.0 BY-SA版权

文章标签：

#LISTAGG #XMLAGG #聚合函数 #分组 #多行变一行 #多行变一行

Oracle 专栏收录该内容

12 篇文章

订阅专栏

1.使用条件查询查询部门为20的员工列表
-- 查询部门为20的员工列表

SELECT t.DEPTNO,t.ENAME FROM SCOTT.EMP t where t.DEPTNO = '20';

效果：

2.使用 listagg() WITHIN GROUP () 将多行合并成一行

SELECT T.DEPTNO, 
     LISTAGG (T.ENAME, ',') WITHIN GROUP (ORDER BY T.ENAME) AS NAMES
FROM SCOTT.EMP T
WHERE T.DEPTNO = '20'
GROUP BY T.DEPTNO;

效果：

3. 使用 listagg() within GROUP () over 将多行记录在一行显示

SELECT
    T.DEPTNO,
    LISTAGG(T.ENAME,',') WITHIN GROUP (ORDER BY T.ENAME) OVER(PARTITION BY T.DEPTNO) as ENAME
FROM
  SCOTT.EMP T WHERE T.DEPTNO = '20';

效果：

4、XMLAGG

说明：xmlagg相比较listagg可以处理超过4000字符的字符串，返回的是clob类型数据。

语句一：结果中的聚合字段是CLOB类型，是文本字符。处理400多万条数据，运行时间是20分钟。

SELECT T.DEPTNO,LTRIM(XMLAGG(XMLELEMENT(T,',',T.ENAME)ORDER BY T.ENAME).EXTRACT('//text()').GETCLOBVAL(),',') AS NAMES 
  FROM SCOTT.EMP T 
 WHERE T.DEPTNO=20
GROUP BY T.DEPTNO;

如下：

语句二：结果汇总的聚合字段也是clob类型，不过是hex字节；处理400多万条数据，运行时间是56秒

SELECT T.DEPTNO,
       RTRIM(XMLAGG(XMLPARSE(CONTENT T.ENAME || ','WELLFORMED) ORDER BY T.ENAME).GETCLOBVAL(),',')  AS NAMES
  FROM SCOTT.EMP T 
 WHERE T.DEPTNO=20 
GROUP BY T.DEPTNO;

效果：