Oracle列转行 参数动态传入iBatis使用示例

Oracle行转列 参数动态传入iBatis使用示例

 

最近做了一个需求，需要获取工作流数据的各个节点的渠道数量信息，各渠道的费用信息~

之前的需求是只需要获取渠道数据，所以做了渠道兼容，每个渠道数量的获取都是先case when 处理，然后再sum统计的

 

方案一：手动汇总数据为列数据（先case when 计算再sum统计）

例如：   #统计渠道数据量：渠道代码相同时，渠道数据计数1，不同时计数0

select
taskname
, case when t.CHANNEL_CODE = #channelcode0# then 1 else 0 end  channel0 
, case when t.CHANNEL_CODE = #channelcode1# then 1 else 0 end  channel1 
from tablename ; 

　　

完整的统计sql

select 
tb.taskname
,sum(tb.channel0) channel0num
,sum(tb.channel1) channel1num
from (
　　select
　　　　taskname
　　　　, case when t.CHANNEL_CODE = #channelcode0# then 1 else 0 end  channel0 
　　　　, case when t.CHANNEL_CODE = #channelcode1# then 1 else 0 end  channel1 
　　from tablename 
)tb group by taskname　

执行效果图如下：

 

方案二：使用oracle的列转行函数

 接下来我们看看强大的Oracle如何教我们行列转置，免得那么麻烦的去case when 再 sum了

 关键函数 pivot  (列转为行) 

 在表名后面接以下这段 pivot( sum(colum_name) alisname for key_column  in (value1 name1, value2 name2))

 colum_name #需要统计的数据信息（渠道数据或者保费信息），这里需要用聚合函数

 alisname  #这个数据项的别名

 key_column #关键列，就是将此列数据转换为行

 value1:  key_column的可能值，这里如果是固定值可以直接写，在代码里面也可以用变量代替

 name1:  value1对应的别名，最后生成的数据名称会自动拼接为  name1_alisname  （例如如下的 ch0_cnt ）

使用示例：

select 
TASK_NAME
,sum(ch0_cnt) as channel0num
,sum(ch1_cnt) as channel1num  
from tablename t pivot(count(CHANNEL_CODE) cnt for CHANNEL_CODE in (1 ch0, 21 ch1))
group by TASK_NAME

　　

执行效果：（与方案一 殊途同归，但是这个会简洁很多，尤其是当渠道信息多的时候就会简洁的更明显了啦~）

 

行转列的时候统计多项数据：（可以写多个聚合函数，但是统计的中心列只有一项哦 以下示例均以 CHANNEL_CODE 渠道为中心，来统计各项数据）

select 
TASK_NAME
,sum(ch0_cnt) as channel0num
,sum(ch1_cnt) as channel1num  
,sum(ch0_daycount) as ch0_daycount  
,sum(ch1_daycount) as ch1_daycount  
from ReportNBUWData t pivot( sum(PASSED_TIME) daycount, count(CHANNEL_CODE) cnt for CHANNEL_CODE in (1 ch0, 21 ch1))
group by TASK_NAME

　　

 方案三：ibatis使用动态变量

变量格式：<![CDATA[$channelcode0$]]>  ，其中 channelcode0 为变量名

千万要记得获取的变量的时候用 $parameter$ ，且要使用 <![CDATA[ ]]> 文本化

例如:

select 
TASK_NAME
,sum(ch0_cnt) as channel0num
,sum(ch1_cnt) as channel1num  
,sum(ch0_daycount) as ch0_daycount  
,sum(ch1_daycount) as ch1_daycount  
from ReportNBUWData t pivot( sum(PASSED_TIME) daycount, count(CHANNEL_CODE) cnt for CHANNEL_CODE in (<![CDATA[$channelcode0$]]>  ch0,<![CDATA[$channelcode1$]]>  ch1))
group by TASK_NAME

　　

如果有多个变量参数，可以按照如下格式继续添加 ： <![CDATA[$channelcode0$]]> ch0

需要注意的点：

1.入参不可以为空！！！ 入参不可以为空！！！  一定要有具体的值！！！  意思就是 $channelcode0$ 变量对应的值不可以为空，也不可以为null！！！

2.要使用 <![CDATA[ ]]> 文本化  （否则就报错：pivot内不能使用动态变量）

3.不可以使用预编译 #channelcode0#,要使用 $channelcode0$！！！ （否则就报错：pivot内不能使用动态变量）

 

参考博客：https://www.bbsmax.com/A/WpdK4oZnzV/ （oracle行转列、列转行、连续日期数字实现方式及mybatis下实现方式）