Usage of listagg function

Usage of listagg function

This is an Oracle column-to-row function: LISTAGG()

Look at the sample code first:

Sql code

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

This is the most basic usage:

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

The usage is Like an aggregate function, a field of each Group is spliced ​​together through the Group by statement.

Very convenient.

is also an aggregate function, and there is an advanced usage:

is over(partition by XXX)

In other words, it is not practical for you When using the Group by statement, you can also use the LISTAGG function:

Sql code

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

Summary: LISTAGG() just use it as the SUM() function .

The above is the detailed content of Usage of listagg function. For more information, please follow other related articles on the PHP Chinese website!