ORACLE列转行函数总结 LISTAGG WITHIN GROUP( ORDER BY )、wm_concat、sys_connect_by_path

最新推荐文章于 2025-03-27 13:33:35 发布

LETIAN_Z

最新推荐文章于 2025-03-27 13:33:35 发布

阅读量2k

点赞数

分类专栏： Oracle 文章标签： oracle 列转行 LISTAGG(XXX wm_concat sys_connect_by_path

本文链接：https://blog.youkuaiyun.com/LETIAN_Z/article/details/103097776

版权

Oracle 专栏收录该内容

4 篇文章

订阅专栏

本文介绍了Oracle数据库中几种常用的数据聚合方法，包括LISTAGG函数的基础与高级应用、wm_concat函数和sys_connect_by_path函数，展示了如何将多行数据合并为一行，以及创建树状结构的字符串。这些技巧对于处理大量数据并进行高效聚合操作非常有用。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

1. LISTAGG(XXX,XXX) WITHIN GROUP( ORDER BY XXX)

基础用法：LISTAGG(XXX,XXX) WITHIN GROUP( ORDER BY XXX)，就像聚合函数一样，通过Group by语句，把每个Group的一个字段，拼接起来；LISTAGG()其实可以把它当作SUM()函数来使用或者理解。

示例代码：

查询部门为20的员工列表:SELECT t.DEPTNO,t.ENAME FROM SCOTT.EMP t where t.DEPTNO = '20';

结果：
在这里插入图片描述
使用 listagg() WITHIN GROUP () 将多行合并成一行：
示例代码：


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

结果：
在这里插入图片描述

2.高级用法：over(partition by XXX)。在不使用Group by语句时候，也可以使用LISTAGG函数：
示例代码：

WITH TEMP AS(  
SELECT 500 POPULATION, 'CHINA' NATION ,'GUANGZHOU' CITY FROM DUAL UNION ALL  
SELECT 1500 POPULATION, 'CHINA' NATION ,'SHANGHAI' CITY FROM DUAL UNION ALL  
SELECT 500 POPULATION, 'CHINA' NATION ,'BEIJING' CITY FROM DUAL UNION ALL  
SELECT 1000 POPULATION, 'USA' NATION ,'NEW YORK' CITY FROM DUAL UNION ALL  
SELECT 500 POPULATION, 'USA' NATION ,'BOSTOM' CITY FROM DUAL UNION ALL  
SELECT 500 POPULATION, 'JAPAN' NATION ,'TOKYO' CITY FROM DUAL   
)  
SELECT POPULATION,  
NATION,  
CITY,  
LISTAGG(CITY,',') WITHIN GROUP (ORDER BY CITY) OVER (PARTITION BY NATION) RANK  
FROM TEMP

结果：
在这里插入图片描述

2. wm_concat(column)

介绍：其函数在Oracle 10g推出，在10g版本中，返回字符串类型，在11g版本中返回clob类型。括号里面的参数是列，而且可以是多个列的集合，也就是说在括号里面可以自由地用‘||’合并字符串
示例代码：

select aaa.aaa_id, wm_concat(aaa.line_id) as ids  
  from  aaa  
 group by aaa.aaa_id;

结果：
在这里插入图片描述

3.sys_connect_by_path(column,<分隔符>)

介绍：其函数在Oracle 9i 版本中推出，用来合并链路的字符串。注意的是其一定要和connect by子句合用！
第一个参数是形成树形式的字段，第二个参数是父级和其子级分隔显示用的分隔符。
示例：SYS_CONNECT_BY_PATH (column, ‘/’) START WITH CONNECT BY PRIOR
示例代码

SELECT * FROM STD_GB_XZQH ORDER BY code

结果：
在这里插入图片描述
现在我想要：‘北京市/市辖区/东城区’这种类型的数据。怎么办？

示例代码：

SELECT substr(SYS_CONNECT_BY_PATH (X. NAME, '/'),2) HJD,X.CODE
FROM STD_GB_XZQH X WHERE X.LEVNUM <> '4' START WITH X.PARENTCODE IS NULL 
CONNECT BY PRIOR X.CODE = X.PARENTCODE

结果：
在这里插入图片描述