首页 > 代码库 > 2015.12.24(圣诞节) 解决Oralce数据库将具有相同属性的多行合并为一行的简单方法多年想要wmsys.wm_concat

2015.12.24(圣诞节) 解决Oralce数据库将具有相同属性的多行合并为一行的简单方法多年想要wmsys.wm_concat

用到Oralce10g以后增加的函数wmsys.wm_concat

技术分享

例如这张表的有两个字段,要按airport_id合并成两行可用sql语句

select airport_id,   wmsys.wm_concat( account) from AIRPORT_MODIFY group by airport_id

结果为:

 技术分享

非常简单,美中不足的是合并结果没有排序,解决排序问题最简单的办法就是在合并列前加上distinct

 技术分享

此外,如果合并列结果出现乱码,需要在合并列前加上to_char(),如wmsys.wm_concat( to_char(account)),据说是因为该函数在不同的Oracle版本中会返回varchar2或CLob两种结果

 

2016.8.15补充,关于排序

按wmsys.wm_concat(distinct account)排序只能实现按account文本排序,无法实现按某复杂逻辑排序。下面是实现方法

有普通sql如下

select flt,srtnum,seg,tran_level,name

from flight_tran_direction                    

where seg>0

order by flt,seg,srtnum

结果为:列举所有航线中,高度层反配的所有点

 技术分享

要实现按flt,seg分组并按srtsum排序的各点串,可写sql为

select flt,seg,tran_level,

WMSYS.WM_CONCAT(name) OVER(PARTITION BY flt,seg ORDER BY srtnum) KEY

from flight_tran_direction                    

where seg>0

order by flt,seg

结果为:类似于group by flt,seg实现了按flt,seg分组,且name按srtnum逻辑排序。唯一不足的是一个分组会多出若干历史行。实际上只需要每组最后一行。

 技术分享

改进sql为:外面再嵌套一层groupby结构,通过max实现取最多的合并列

select flt,seg,max(key) rt

from

 (

 select flt,seg,tran_level,

 WMSYS.WM_CONCAT(name) OVER(PARTITION BY flt,seg ORDER BY srtnum) KEY

 from flight_tran_direction                    

 where seg>0

 order by flt,seg

)

group by flt,seg,tran_level

结果为:

 技术分享

甚至反过按合并列rt分组,组合flt

select rt,tran, WMSYS.WM_CONCAT( distinct flt)

from

(

select flt,seg,

case mod(to_number(regexp_substr(tran_level,‘^[[:digit:]]+‘)),2) when 1 then ‘西单‘ else ‘东双‘ end tran,

replace(max(key),‘,‘,‘-‘) rt

from

(

select flt,seg,tran_level,

WMSYS.WM_CONCAT(name) OVER(PARTITION BY flt,seg ORDER BY srtnum) KEY

from flight_tran_direction                    

where seg>0

order by flt,seg

)

group by flt,seg,tran_level

)

group by rt,tran

order by rt,tran

技术分享

2015.12.24(圣诞节) 解决Oralce数据库将具有相同属性的多行合并为一行的简单方法多年想要wmsys.wm_concat