快捷導(dǎo)航

oracle中行轉(zhuǎn)列LISTAGG()函數(shù)詳解及應(yīng)用實例

更新時間：2024年05月27日 09:18:08 作者：sym@12

這篇文章主要給大家介紹了關(guān)于oracle中行轉(zhuǎn)列LISTAGG()函數(shù)詳解及應(yīng)用實例的相關(guān)資料,stagg是oracle11.2增加的特性,功能類似wmsys.wm_concat函數(shù),即將數(shù)據(jù)分組后,把指定列的數(shù)據(jù)通過指定符號合并,需要的朋友可以參考下

1.LISTAGG()函數(shù)作為普通函數(shù)使用時就是查詢出來的結(jié)果列轉(zhuǎn)為行

SELECT
	LISTAGG ( NAME_CHS, ',' ) within GROUP ( ORDER BY ROWNUM ) name 
FROM
	GSPUSER 
WHERE
	ROWNUM <= 10

2.LISTAGG()作為分組函數(shù)使用

例如，把每個班組下面的人員拼接成一行

SELECT
	b.MOMTEAMNAME,
	LISTAGG ( g.NAME_CHS, ',' ) WITHIN GROUP ( ORDER BY b.MOMTEAMNAME ) res
FROM
	DGMOMPTDGMOMGLHQYBZGL b 
	LEFT JOIN DGMOMPTABOUTUSERS u ON b.ID = u.PARENTID 
	LEFT JOIN GSPUSER g ON u.MOMEMPLOYEEID = g.ID 
WHERE
	b.MOMDATASTATE = 0 
	AND u.MOMDATASTATE = 0 
GROUP BY
	b.MOMTEAMNAME

3.LISTAGG()做分析函數(shù)使用

例如，查詢每個班組下面有哪些人，統(tǒng)計每組人數(shù)量

SELECT
    MOMTEAMNAME,RES,COUNT
FROM(	
 SELECT
	b.MOMTEAMNAME,
	LISTAGG ( g.NAME_CHS, ',' ) WITHIN GROUP ( ORDER BY b.MOMTEAMNAME ) over(partition by b.MOMTEAMNAME) res,
	count(g.NAME_CHS) over(partition by b.MOMTEAMNAME) count,
	row_number() over(partition by b.MOMTEAMNAME ORDER BY ROWNUM) rn
FROM
	DGMOMPTDGMOMGLHQYBZGL b 
	LEFT JOIN DGMOMPTABOUTUSERS u ON b.ID = u.PARENTID 
	LEFT JOIN GSPUSER g ON u.MOMEMPLOYEEID = g.ID 
WHERE
	b.MOMDATASTATE = 0 
	AND u.MOMDATASTATE = 0 
) WHERE rn = 1

附：高級用法

listagg(XXX,’,’) within GROUP (order by XXX) over (partition by XXX) rank

示例

with temp as(  
select 500 population, '中國' nation ,'江蘇' city from dual union all  
select 1500 population, '中國' nation ,'上海' city from dual union all  
select 500 population, '中國' nation ,'北京' city from dual union all  
select 1000 population, '美國' nation ,'紐約' city from dual union all  
select 500 population, '美國' nation ,'波士頓' city from dual union all  
select 500 population, '日本' nation ,'東京' city from dual   
)  
select population,  
nation,  
city,  
listagg(city,',') within GROUP (order by city) over (partition by nation) rank  
from temp復(fù)制

運(yùn)行結(jié)果

總結(jié)

到此這篇關(guān)于oracle中行轉(zhuǎn)列LISTAGG()函數(shù)詳解及應(yīng)用實例的文章就介紹到這了,更多相關(guān)oracle行轉(zhuǎn)列LISTAGG()內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

您可能感興趣的文章: