使用MySQL子查询执行除法操作时遭遇1064语法错误求助

问题描述 #

执行以下UPDATE语句时触发语法错误：

Error Code: 1064. You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ')' at line 1

原执行代码：

UPDATE national_avg_tariff SET Value= (CASE WHEN ((SELECT * FROM((SELECT Value 
                                                    / ((Select Sum_CPP_MlnRs from ppp_fy2020_2030_annex4.FY_CPP_exclKE_MlnRs where FiscalYear=2020) + (Select UoSC_MoF_exclKE_MlnRs from ppp_fy2020_2030_annex4.FY_UoSC_MoF_exclKE_MlnRs where FiscalYear=2020))/ (SELECT Sum(Sum_of_AvgMW) FROM ppp_fy2020_2030_annex4.AVG_OF_FY_MAX_DEMAND where FiscalYear=2020 GROUP BY FiscalYear)FROM tariff_true_cost.national_avg_tariff WHERE Sector='Residential' AND Category='Up to 50 Units' AND Parameter= 'Fixed Charge(Rs/kW/M)' AND FiscalYear=2020)temptbl8))>0)
                                                    THEN 
                                                    (SELECT * FROM((SELECT Value 
                                                    / ((Select Sum_CPP_MlnRs from ppp_fy2020_2030_annex4.FY_CPP_exclKE_MlnRs where FiscalYear=2020) + (Select UoSC_MoF_exclKE_MlnRs from ppp_fy2020_2030_annex4.FY_UoSC_MoF_exclKE_MlnRs where FiscalYear=2020))/ (SELECT Sum(Sum_of_AvgMW) FROM ppp_fy2020_2030_annex4.AVG_OF_FY_MAX_DEMAND where FiscalYear=2020 GROUP BY FiscalYear)FROM tariff_true_cost.national_avg_tariff WHERE Sector='Residential' AND Category='Up to 50 Units' AND Parameter= 'Fixed Charge(Rs/kW/M)' AND FiscalYear=2020)temptbl8))
                                                    ELSE NULL END) where FiscalYear=2020 AND Parameter='Fixed Charge(%)'; 

错误原因分析 #

1. 冗余嵌套与括号不匹配：原语句使用多层不必要的子查询嵌套（如SELECT * FROM((SELECT ...) temptbl8)），导致括号层级混乱，MySQL无法正确解析。
2. 单值子查询误用SELECT *：CASE判断需要单个数值，但原语句用SELECT *返回整行，不符合语法要求。
3. 重复计算逻辑：相同计算表达式重复书写，既增加语法出错概率，也降低执行效率。

修正后的SQL语句 #

方案1：简化子查询，消除冗余嵌套

UPDATE national_avg_tariff 
SET Value = (
    CASE 
        WHEN (
            SELECT Value 
            / (
                (SELECT Sum_CPP_MlnRs FROM ppp_fy2020_2030_annex4.FY_CPP_exclKE_MlnRs WHERE FiscalYear=2020) 
                + (SELECT UoSC_MoF_exclKE_MlnRs FROM ppp_fy2020_2030_annex4.FY_UoSC_MoF_exclKE_MlnRs WHERE FiscalYear=2020)
            ) 
            / (SELECT SUM(Sum_of_AvgMW) FROM ppp_fy2020_2030_annex4.AVG_OF_FY_MAX_DEMAND WHERE FiscalYear=2020)
            FROM tariff_true_cost.national_avg_tariff 
            WHERE Sector='Residential' 
              AND Category='Up to 50 Units' 
              AND Parameter= 'Fixed Charge(Rs/kW/M)' 
              AND FiscalYear=2020
        ) > 0 
        THEN (
            SELECT Value 
            / (
                (SELECT Sum_CPP_MlnRs FROM ppp_fy2020_2030_annex4.FY_CPP_exclKE_MlnRs WHERE FiscalYear=2020) 
                + (SELECT UoSC_MoF_exclKE_MlnRs FROM ppp_fy2020_2030_annex4.FY_UoSC_MoF_exclKE_MlnRs WHERE FiscalYear=2020)
            ) 
            / (SELECT SUM(Sum_of_AvgMW) FROM ppp_fy2020_2030_annex4.AVG_OF_FY_MAX_DEMAND WHERE FiscalYear=2020)
            FROM tariff_true_cost.national_avg_tariff 
            WHERE Sector='Residential' 
              AND Category='Up to 50 Units' 
              AND Parameter= 'Fixed Charge(Rs/kW/M)' 
              AND FiscalYear=2020
        )
        ELSE NULL 
    END
) 
WHERE FiscalYear=2020 
  AND Parameter='Fixed Charge(%)';

方案2：提取公共计算值，提升可读性与效率

-- 定义变量存储公共计算结果
SET @total_cost = (
    SELECT Sum_CPP_MlnRs + UoSC_MoF_exclKE_MlnRs
    FROM ppp_fy2020_2030_annex4.FY_CPP_exclKE_MlnRs cpp
    CROSS JOIN ppp_fy2020_2030_annex4.FY_UoSC_MoF_exclKE_MlnRs usc
    WHERE cpp.FiscalYear=2020 AND usc.FiscalYear=2020
);

SET @total_avg_mw = (
    SELECT SUM(Sum_of_AvgMW) 
    FROM ppp_fy2020_2030_annex4.AVG_OF_FY_MAX_DEMAND 
    WHERE FiscalYear=2020
);

SET @calculated_value = (
    SELECT Value / @total_cost / @total_avg_mw
    FROM tariff_true_cost.national_avg_tariff 
    WHERE Sector='Residential' 
      AND Category='Up to 50 Units' 
      AND Parameter= 'Fixed Charge(Rs/kW/M)' 
      AND FiscalYear=2020
);

-- 执行更新
UPDATE national_avg_tariff 
SET Value = CASE WHEN @calculated_value > 0 THEN @calculated_value ELSE NULL END
WHERE FiscalYear=2020 
  AND Parameter='Fixed Charge(%)';

关键修正点说明 #

• 移除不必要的SELECT * FROM((...))嵌套结构，直接使用单值子查询返回计算结果。
• 将重复计算逻辑提取为变量，减少代码冗余，降低语法出错概率。
• 确保CASE语句的判断条件和返回值均为单个数值，符合MySQL对子查询的语法要求。

内容的提问来源于stack exchange，提问作者ar ia