Sql Server 中 PIVOT在mysql 中的实现

本文介绍在SQL Server中使用PIVOT函数实现行列转换的方法,并提供了将此功能移植到MySQL的具体步骤,包括创建临时表及使用CASE WHEN语句进行转换。

摘要生成于 C知道 ,由 DeepSeek-R1 满血版支持, 前往体验 >

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;

 

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值