listagg函式的用法

listagg函數的用法

#這是一個Oracle的列轉行函數：LISTAGG()

 

先看範例程式碼：

Sql程式碼 

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函數：

Sql程式碼  

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()函數來使用就可以了。

以上是listagg函式的用法的詳細內容。更多資訊請關注PHP中文網其他相關文章！