Oracle中的 listagg 拼接字符串函数以及 PARTITION BY 分组函数,今天遇到一个问题,要实现一些功能,找了一会才弄明白这两个函数如何使用,记录一下,避免忘记。
一,WM_CONCAT(column)
行转列函数,将多行查询结果拼接成一句话,默认用,
拼接,可以用replace函数替换。
--要求将一部分人的名字拼接起来
SELECT * FROM AAA;
SELECT REPLACE(WM_CONCAT(A02), ',', ',') AS xm from AAA;
--邹代军,周传龙,王忠华,王晓东,蔡宝龙,庄许洪,朱明红,赵雄伟,赵振虎,刘森然,张 赛,尹丽娜
二,LISTAGG(column) WITHIN GROUP (ORDER BY column)
行转列函数,将多行查询结果拼接成一句话,和wm_concat(column)
函数很像,但是在数据量特别大的时候,需要分组时,LISTAGG更适合。
假设现在有一张个人奖励表,现在要把每个人获奖情况按照获奖时间排序然后,拼接出来,如:朱明红 个人三等功,个人二等功
--LISTAGG(column, '-'),后面的参数是指定拼接起来的连接符,WITHIN GROUP(ORDER BY colnum)括号中可指定拼接顺序
SELECT A02, LISTAGG(A04, ',') WITHIN GROUP (ORDER BY A03) AS JL FROM AAA GROUP BY A02;
三,OVER (PARTITION BY)
ROW_NUMBER() OVER ( PARTITION BY column1 ORDER BY column 2 )
,
意思是将查询结果根据column1
进行分组,然后内部根据column2
进行排序,得到的结果就是每组内部排好序的结果,可用于去重复值,或者从分组数据中提取指定规则的数据。与rownum
的区别在于:使用rownum
进行排序的时候是先对结果集加入伪列rownum
然后再进行排序,而该函数在包含排序从句后是先排序再计算行号码。
现在有一张个人奖励明细表,A01代表人员编号,A02姓名,A03获奖时间,A04奖励名称,需求:查出每个人最近的一次授予奖励是什么。
--要查出每个人最近的奖励,那就得先将人分组分出来,然后在按照获奖时间进行倒序排序,拿到第一条就是最近的获奖记录
SELECT
A.A02, A.A03, A.A04
FROM
(SELECT
AAA.A02,
AAA.A03,
AAA.A04,
ROW_NUMBER() OVER (PARTITION BY AAA.A01 ORDER BY AAA.A03 DESC) AS R
FROM AAA
) A
WHERE A.R = 1
--指定 R=1 就能取到每个分组中的第一条数据了,如果想要多条改这个参数即可