Oracle使用listagg解决wm_concat函数排序问题(及in排序问题)
问题:我们在使用in运算查询时,Oracle会进行默认排序。无法按照in条件顺序输出
例如:
/*
id sname
1 AA
1239 BB
2 CC
*/
select sname from user where id in (1,1239,2)
--期望:AA BB CC
--结果:AA CC BB
可见查询结果是根据条件进行排序的,于是改进一下代码,使用decode进行排序
select sname from user where id in (1,1239,2) ORDER BY DECODE(id, 1,1239,2)
--结果显示为:AA BB CC
可见使用decode可以解决in运算带来的排序问题!
But!
当在使用wm_concat函数的场景时
select wmsys.wm_concat(sname) from user where id in (1,1239,2) ORDER BY DECODE(id, 1,1239,2)
--结果: AA,CC,BB
排序又出现了问题...原因是wm_concat函数本身会对结果集进行一次排序,而且在Oracle 12c版本中已经废弃了wm_concat函数
于是想到了用listagg代替wm_concat函数
listagg() 可接收两个参数,聚合列 和 分隔符,不写分隔符参数即无分隔符直接拼接。
within group(order by xx)里面的就是聚合列拼接顺序的排序,用法跟普通排序order by 一样
select listagg(sname, ',') within group(order by decode(id,1,1239,2) desc)
from (
select id,sname from user where id in (1,1239,2) --order by decode(id,1,1239,2) desc
)
--结果:AA,CC,BB
好吧看来within group()是不支持这种写法, 于是尝试将内层查询排序好再使用rownum识别排序
select listagg(sname, ',') within group(order by rownum)
from (
select id,sname from user where iid in (1,1239,2)
order by decode(id,1,1239,2) desc
)
--结果:AA,BB,CC
如果去掉一个条件
select listagg(sname, ',') within group(order by rownum)
from (
select id,sname from user where iid in (1,1239)
order by decode(id,1,1239) desc
)
--结果:CC,AA
发现还是有问题!再试试instr吧
select listagg(sname, ',') within group(order by rownum)
from (
select id,sname from user where iid in (1,1239)
order by instr('1,1239',iid)
)
--结果 CC,AA
--考虑到id值逗号的问题,拼上逗号
select listagg(sname, ',') within group(order by rownum)
from (
select id,sname from user where iid in (1,1239)
order by instr(',1,1239,',','||iid||',')
)
--结果 AA,CC
这回终于解决
总结:
在in运算场景中使用order by instr()进行排序,可以按照in条件顺序输出。
在wm_concat场景使用listagg代替。