欢迎您访问 最编程 本站为您分享编程语言代码，编程技术文章！

热门搜索/Hot Search

您现在的位置是： 首页

使用 LISTAGG 和 OVER PARTITION BY：函数的详解与应用

最编程 2024-08-08 12:53:53
...

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 就能取到每个分组中的第一条数据了，如果想要多条改这个参数即可

在这里插入图片描述

上一篇： 搞定Oracle listagg的去重和distinct操作，这里有三种实用方法！

下一篇： 玩转Oracle EBS：如何轻松禁用表单的其它修改功能？

推荐阅读