当前位置：首页 数据库正文

oracle中行转列LISTAGG()函数的用法

2024-11-21 1

1.LISTAGG()函数作为普通函数使用时就是查询出来的结果列转为行

1
2
3
4
5
6
SELECT
    LISTAGG ( NAME_CHS, ',' ) within GROUP ( ORDER BY ROWNUM ) name
FROM
    GSPUSER
WHERE
    ROWNUM <= 10

2.LISTAGG()作为分组函数使用

例如，把每个班组下面的人员拼接成一行

1
2
3
4
5
6
7
8
9
10
11
12
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()做分析函数使用

例如，查询每个班组下面有哪些人，统计每组人数量

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
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

示例

1
2
3
4
5
6
7
8
9
10
11
12
13
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复制

运行结果


相关文章

热门排行