oracle的行列转换函数,wm_concat()方法与的排序问题,Oracle的 listagg 函数
wm_concat聚合无法直接排序聚合的问题和oracle 11g的新函数listagg。
Oracle中的 wm_concat() 函数的使用
功能是:实现行转列功能,即将查询出的某一列值使用逗号进行隔开拼接,成为一条数据。
如:
![](https://img2020.cnblogs.com/i-beta/1083711/202003/1083711-20200312165909258-2108352987.png)
![](https://img2020.cnblogs.com/i-beta/1083711/202003/1083711-20200312171902165-1652485059.png)
select u_id, to_char(wmsys.wm_concat(distinct name ) ) as name from thomas group by charge_code
![](https://img2020.cnblogs.com/i-beta/1083711/202003/1083711-20200312171844594-56059574.png)
select u_id, wmsys.wm_concat(name || '(' || num || '斤)' ) as name from shopping group by u_id
![](https://img2020.cnblogs.com/i-beta/1083711/202003/1083711-20200312170204672-485802685.png)
上面charge_code为2的时,有两个梨子,如何实现 梨子(11)呢?
=============================================================================
2、需求:
关于wm_concat聚合无法直接排序聚合的问题和oracle 11g的新函数listagg。
3、准备数据:
需要的结果是这样,对gradenm列按gradeno列进行排序合并
4、实现
(1)wm_concat() 聚合,代码及结果如下:
这个时候很明显看到,wm_concat默认是没有办法给gradenm排序的,当然你可以把from table的部分先查出进行排序,
再进行wm_concat聚合,我只能告诉你这样并没有什么卵用。
而且wm_concat默认是用逗号进行字符串拼接,如果需求是别的分隔符,还得用replace去替换一下。
(2)尝试用wm_concat()over(partition by xx order by xx) 分析函数改进,代码及结果如下:
通过分析函数的patition进行分区分组,然后排序是order by gradeno,这样,嗯。。看结果。然后我们外面套上一层select 查询这个结果,按schooltype分组,取grade最长的,就可以查到想要的结果了。代码如下:
select schooltype, max(grade) grade
from (select schooltype, gradeno, wm_concat(gradenm) over(partition by schooltype order by gradeno) grade
from schoolgrade a)
group by schooltype;
(3)当然,不可能就这么结束了,今天的主角其实是listagg函数,
Oracle Database 11g开始提供的一个聚合函数,配合分组实现上面的需求,代码和结果如下:
--listagg()within group(order by xx)
select schooltype, listagg(gradenm, '、') within group(order by gradeno) gradenm
from schoolgrade
group by schooltype;
listagg() 可接收两个参数,聚合列 和 分隔符,不写分隔符参数即无分隔符直接拼接。
within group(order by xx)里面的就是聚合列拼接顺序的排序,用法跟普通排序order by 一样。
group by,分组,按组统计。有用到分组的才需要。
****************************************
补充:wm_concat是在wmsys用户下的一个函数,是oracle的一个非公开的函数,而且,wm_concat在前面几个版本中的返回值数据类型也不相同,在新版本12c中更是直接被抛弃了。
所以,日常开发中并不建议使用wm_concat。强行建议使用listagg。要么就自己写个函数用。
原文链接:https://blog.csdn.net/Huay_Li/article/details/81257655