mysql怎么实现pivot_Sql Server 中 PIVOT在mysql 中的实现 - 代码咸鱼梦 - 博客园

sql server 中项目代码：

select isnull(MI线,0) as F_MIValue,isnull(SMT线,0) as F_SMTValue,isnull(空压站,0) as F_ChangePre,

isnull(Repair线,0) as F_RepairValue,isnull(Assembly线,0) as F_AssemblyValue

from (select F_Name,CONVERT(decimal(16,2),sum(F_Value)) as F_Value from EnergyValue

where F_Name like '%MI%' or F_Name like '%SMT%'or F_Name like '%Repair%' or F_Name like '%Assembly%' or F_Name = '空压站'

group by F_Name) aa

pivot(max(F_Value)for F_Name in(MI线,SMT线,空压站,Repair线,Assembly线))a复制代码

而类似下面

PIVOT (MAX (F_ZZ_BaseValue) FOR F_Flag IN (F, S)) AS S ; # Mysql不支持PIVOT函数进行行列转换，故需要通过sql语句进行转换。

实现代码如下：

CREATE TEMPORARY TABLE IF NOT EXISTS T_TmpPivot

(

select F_Name,CONVERT(sum(F_Value),decimal(16,2)) as F_Value from EnergyValue

where F_Name like '%MI%' or F_Name like '%SMT%'or F_Name like '%Repair%' or F_Name like '%Assembly%' or F_Name = '空压站'

group by F_Name

);

select ifnull(MI线,0) as F_MIValue,ifnull(SMT线,0) as F_SMTValue,ifnull(空压站,0) as F_ChangePre,

ifnull(Repair线,0) as F_RepairValue,ifnull(Assembly线,0) as F_AssemblyValue

from(

SELECT MAX(MI线) AS MI线,MAX(SMT线) AS SMT线,MAX(空压站) AS 空压站,MAX(Repair线) AS Repair线,MAX(Assembly线) AS Assembly线 FROM

(

SELECT

CASE F_Name WHEN 'MI线' THEN F_Value END AS MI线,

CASE F_Name WHEN 'SMT线' THEN F_Value END AS SMT线,

CASE F_Name WHEN '空压站' THEN F_Value END AS 空压站,

CASE F_Name WHEN 'Repair线' THEN F_Value END AS Repair线,

CASE F_Name WHEN 'Assembly线' THEN F_Value END AS Assembly线

FROM T_TmpPivot

) AS tmp

) AS T;

DROP TEMPORARY TABLE IF EXISTS T_TmpPivot;复制代码