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()函數來使用就可以了。
? 版權聲明
文章版權歸作者所有，未經允許請勿轉載。
THE END
