`
dacoolbaby
  • 浏览: 1264690 次
  • 性别: Icon_minigender_1
  • 来自: 广州
社区版块
存档分类
最新评论

Oracle 列转行函数 Listagg()

 
阅读更多

这是一个Oracle的列转行函数:LISTAGG()

 

先看示例代码:

with temp as(
  select 'China' nation ,'Guangzhou' city from dual union all
  select 'China' nation ,'Shanghai' city from dual union all
  select 'China' nation ,'Beijing' city from dual union all
  select 'USA' nation ,'New York' city from dual union all
  select 'USA' nation ,'Bostom' city from dual union all
  select 'Japan' nation ,'Tokyo' city from dual 
)
select nation,listagg(city,',') within GROUP (order by city)
from temp
group by nation

 这是最基础的用法:

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

 

用法就像聚合函数一样,通过Group by语句,把每个Group的一个字段,拼接起来。

非常方便。

 

同样是聚合函数,还有一个高级用法:

就是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

 

总结:LISTAGG()把它当作SUM()函数来使用就可以了。

 

 

 

 

分享到:
评论
5 楼 pktangshao 2017-03-08  
a_bun 写道
iijjll 写道
使用wmsys.wm_concat()函数也行

with temp as( 
  select 'China' nation ,'Guangzhou' city from dual union all 
  select 'China' nation ,'Shanghai' city from dual union all 
  select 'China' nation ,'Beijing' city from dual union all 
  select 'USA' nation ,'New York' city from dual union all 
  select 'USA' nation ,'Bostom' city from dual union all 
  select 'Japan' nation ,'Tokyo' city from dual  

select nation,wmsys.wm_concat(city)
from temp 
group by nation

wm_concat函数慎用,性能很差,会产生很多block的

wm_concat实在是少用为好,今天是遇到了,SQL中执行很快,到了存储过程超级慢.慢10倍以上.找了好久才找到原来是这个函数这里慢
谢谢博主提供的这个LISTAGG函数.
本来我在最后GROUP的,看了楼主的写法改造成OVER(PARTITAION BY)了,在同样的数据情况下比在最后GROUP快了6秒.
4 楼 a_bun 2016-08-11  
iijjll 写道
使用wmsys.wm_concat()函数也行

with temp as( 
  select 'China' nation ,'Guangzhou' city from dual union all 
  select 'China' nation ,'Shanghai' city from dual union all 
  select 'China' nation ,'Beijing' city from dual union all 
  select 'USA' nation ,'New York' city from dual union all 
  select 'USA' nation ,'Bostom' city from dual union all 
  select 'Japan' nation ,'Tokyo' city from dual  

select nation,wmsys.wm_concat(city)
from temp 
group by nation

wm_concat函数慎用,性能很差,会产生很多block的
3 楼 iijjll 2015-01-20  
使用wmsys.wm_concat()函数也行

with temp as( 
  select 'China' nation ,'Guangzhou' city from dual union all 
  select 'China' nation ,'Shanghai' city from dual union all 
  select 'China' nation ,'Beijing' city from dual union all 
  select 'USA' nation ,'New York' city from dual union all 
  select 'USA' nation ,'Bostom' city from dual union all 
  select 'Japan' nation ,'Tokyo' city from dual  

select nation,wmsys.wm_concat(city)
from temp 
group by nation
2 楼 dacoolbaby 2013-01-15  
Eric.Yan 写道
listagg作为分析函数(即实用over(partition by XXX)关键字)用到的很少,楼主的sql:
select population,  
       nation,  
       city,  
       listagg(city,',') within GROUP (order by city) over (partition by nation) rank  
from temp;

意思是选出跟当前city一样的属于同一个nation的city,合并显示;

你说的没错。
使用或不使用over(partition by xxx)根据你的SQL的复杂度来进行判断。
1 楼 Eric.Yan 2013-01-15  
listagg作为分析函数(即实用over(partition by XXX)关键字)用到的很少,楼主的sql:
select population,  
       nation,  
       city,  
       listagg(city,',') within GROUP (order by city) over (partition by nation) rank  
from temp;

意思是选出跟当前city一样的属于同一个nation的city,合并显示;

相关推荐

    oracle wm_concat 列转行 逗号分隔

    然而,由于WM_CONCAT的局限性,Oracle建议使用其他替代方法,如使用LISTAGG函数(自Oracle 11g R2起提供)或者自定义聚合函数来实现类似的功能。LISTAGG函数提供了更好的控制,比如可以指定分隔符,并且是标准的SQL...

    Oracle行转列

    Oracle行转列 Oracle行转列是指在Oracle...Oracle行转列操作可以使用多种方法来实现,包括UNION ALL、MODEL、COLLECTION、AGGREGATE FUNCTION、LISTAGG、REGEXP_SPLIT等方法。这些方法可以满足不同的应用场景和需求。

    oracle学习参考

    - **字符串的列转行**:对于简单的列转行,可以使用`LISTAGG`函数,但其灵活性有限。推荐使用自定义的函数,如`to_string_limit`,它可以更灵活地控制字符串的排序、去重和长度限制。 在实际工作中,理解并熟练...

    oracle数据行列转换

    3. 列转行:Oracle 10g中,我们通常使用LISTAGG函数(在11g中引入,但在10g可以通过其他方式模拟)或者CONNECT BY语句来实现列转行。例如,如果我们有一个包含多个产品分类的列,希望将其拆分为多行,可以使用以下...

    Oracle 行列转换 总结

    例如,使用 LISTAGG 函数可以将多个列转换成一个字符串。 4. 多行转换成字符串 多行转换成字符串是指将多行转换成一个字符串。例如,使用 LISTAGG 函数可以将多行转换成一个字符串。 5. 字符串转换成多列 字符串...

    Oracle行列转换_总结

    - 使用聚合函数(如 `LISTAGG`)将多行数据连接成一个字符串。 - **字符串转换成多列** - 使用 `SUBSTR` 和 `INSTR` 函数,或者 `REGEXP_SUBSTR` 函数将字符串拆分成多列。 - **字符串转换成多行** - 同样地,...

    数据库行列转换算法

    Oracle数据库提供了多种方法来实现这种转换,本篇将详细介绍如何在Oracle中进行行列转换,包括列转行、行转列以及各种复杂场景下的转换。 1. 列转行 列转行主要是将数据库表中的多列数据转换为多行数据。在Oracle...

    行列转换总结.pdf

    列转行是指将数据表中的列转换为行的操作。这种转换常用于将分类数据展平,以便更好地展示数据之间的关系或进行后续的数据分析。根据给定的部分内容,我们可以通过三种不同的方法来进行列转行操作。 ##### 2.1 使用...

    sql高级进阶

    - 列转行:使用UNPIVOT或其他数据库函数将列数据转换为行数据。 - 将结果集反向转置为一列:将多行数据合并为单个字段。 - 抑制结果集中的重复值:使用DISTINCT关键字。 - 利用“行转列”进行计算:在转换后的...

Global site tag (gtag.js) - Google Analytics