pivot函数迁移之Oracle至MogDB

原作者：罗炳森

• 适用范围
• 问题概述
• 问题原因
• 解决方案

适用范围

Oracle迁移至MogDB

问题概述

在进行Oracle迁移至MogDB的时候，MogDB执行有pivot函数的语句报错

问题原因

MogDB目前不支持pivot函数

解决方案

改写语句，例子如下：

Oracle中pivot语句：

SELECT
"XMBH","XMMC","XMKNX","HTID","HTQYRQ","XSMC","XSBM","XMJL","SRZE",
"YWCSRZE","'业务体系'","'售前体系'","'交付体系'"
FROM
SRCOSTVIEW
pivot(
SUM(systemcost) for system in ('业务体系' as 业务体系,'售前体系'
as 售前体系,'交付体系' as 交付体系)

MogDB中改写语句：

select XMBH,
XMMC,
XMKNX,
HTID,
HTQYRQ,
XSMC,
XSBM,
XMJL,
SRZE,
YWCSRZE,
sum(case
when system = '业务体系' then
systemcost
end) as 业务体系,
sum(case
when system = '售前体系' then
systemcost
end) as 售前体系,
sum(case
when system = '交付体系' then
systemcost
end) as 交付体系
from SRCOSTVIEW
group by XMBH, XMMC, XMKNX, HTID, HTQYRQ, XSMC, XSBM, XMJL, SRZE, YWCSRZE;
)

改写模板：

select groupbycolumn, ---select列 去掉 for ... in 里面的列
sum(case when for后面的列='in里面的别名' then pivot后面的聚合函数里面的列 end) as in里面的别名,
................in里面有多少个别名就有多少个sum case when.................................
sum(case when for后面的列='in里面的别名' then pivot后面的聚合函数里面的列 end) as in里面的别名
from t_score
group by groupbycolumn;