MySQL查询语句语法无误但TotalRecvd字段始终返回0，请求原因排查

你遇到的这个问题，核心是MySQL不允许在同一个SELECT子句中直接引用其他列的别名来进行计算。

在你的查询里，SumOfWK_1和SumOfWK_2是你给聚合列定义的别名，但MySQL的执行顺序是先完成GROUP BY和聚合函数计算，之后才会处理SELECT里的别名赋值。所以当你写(SumOfWK_1 + SumOfWK_2)时，MySQL会把这两个别名当成未初始化的变量，默认值为0，最终导致TotalRecvd总是返回0。

有两种常见的修复方式：

方式1：重复聚合函数表达式 #

直接把SUM(schain_cca_26_week_plan_recv.wk_1)和SUM(schain_cca_26_week_plan_recv.wk_2)的表达式代入计算：

SELECT 
    tblfanuctrainedparts.partnum, 
    tblfanuctrainedparts.cycletime, 
    tblfanuctrainedparts.partname, 
    SUM(schain_cca_26_week_plan_recv.wk_1) AS SumOfWK_1, 
    SUM(schain_cca_26_week_plan_recv.wk_2) AS SumOfWK_2, 
    SUM(schain_cca_26_week_plan_recv.wk_3) AS SumOfWK_3, 
    SUM(schain_cca_26_week_plan_recv.wk_4) AS SumOfWK_4, 
    SUM(schain_cca_26_week_plan_recv.wk_5) AS SumOfWK_5, 
    SUM(schain_cca_26_week_plan_recv.wk_6) AS SumOfWK_6, 
    SUM(schain_cca_26_week_plan_recv.wk_7) AS SumOfWK_7, 
    SUM(schain_cca_26_week_plan_recv.wk_8) AS SumOfWK_8, 
    (SUM(schain_cca_26_week_plan_recv.wk_1) + SUM(schain_cca_26_week_plan_recv.wk_2)) AS TotalRecvd 
FROM schain_cca_26_week_plan_recv 
INNER JOIN tblfanuctrainedparts ON schain_cca_26_week_plan_recv.part_nbr = tblfanuctrainedparts.partnum 
GROUP BY tblfanuctrainedparts.partnum, tblfanuctrainedparts.cycletime, tblfanuctrainedparts.partname

方式2：使用子查询/CTE（更清晰，适合复杂计算） #

如果后续还有更多基于这些别名的计算，用子查询或者CTE先算出聚合结果，再在外部查询里计算合计：

子查询版本： #

SELECT 
    partnum, 
    cycletime, 
    partname, 
    SumOfWK_1, 
    SumOfWK_2, 
    SumOfWK_3, 
    SumOfWK_4, 
    SumOfWK_5, 
    SumOfWK_6, 
    SumOfWK_7, 
    SumOfWK_8, 
    (SumOfWK_1 + SumOfWK_2) AS TotalRecvd 
FROM (
    SELECT 
        tblfanuctrainedparts.partnum, 
        tblfanuctrainedparts.cycletime, 
        tblfanuctrainedparts.partname, 
        SUM(schain_cca_26_week_plan_recv.wk_1) AS SumOfWK_1, 
        SUM(schain_cca_26_week_plan_recv.wk_2) AS SumOfWK_2, 
        SUM(schain_cca_26_week_plan_recv.wk_3) AS SumOfWK_3, 
        SUM(schain_cca_26_week_plan_recv.wk_4) AS SumOfWK_4, 
        SUM(schain_cca_26_week_plan_recv.wk_5) AS SumOfWK_5, 
        SUM(schain_cca_26_week_plan_recv.wk_6) AS SumOfWK_6, 
        SUM(schain_cca_26_week_plan_recv.wk_7) AS SumOfWK_7, 
        SUM(schain_cca_26_week_plan_recv.wk_8) AS SumOfWK_8 
    FROM schain_cca_26_week_plan_recv 
    INNER JOIN tblfanuctrainedparts ON schain_cca_26_week_plan_recv.part_nbr = tblfanuctrainedparts.partnum 
    GROUP BY tblfanuctrainedparts.partnum, tblfanuctrainedparts.cycletime, tblfanuctrainedparts.partname
) AS aggregated_data

CTE版本（MySQL 8.0+支持）： #

WITH aggregated_data AS (
    SELECT 
        tblfanuctrainedparts.partnum, 
        tblfanuctrainedparts.cycletime, 
        tblfanuctrainedparts.partname, 
        SUM(schain_cca_26_week_plan_recv.wk_1) AS SumOfWK_1, 
        SUM(schain_cca_26_week_plan_recv.wk_2) AS SumOfWK_2, 
        SUM(schain_cca_26_week_plan_recv.wk_3) AS SumOfWK_3, 
        SUM(schain_cca_26_week_plan_recv.wk_4) AS SumOfWK_4, 
        SUM(schain_cca_26_week_plan_recv.wk_5) AS SumOfWK_5, 
        SUM(schain_cca_26_week_plan_recv.wk_6) AS SumOfWK_6, 
        SUM(schain_cca_26_week_plan_recv.wk_7) AS SumOfWK_7, 
        SUM(schain_cca_26_week_plan_recv.wk_8) AS SumOfWK_8 
    FROM schain_cca_26_week_plan_recv 
    INNER JOIN tblfanuctrainedparts ON schain_cca_26_week_plan_recv.part_nbr = tblfanuctrainedparts.partnum 
    GROUP BY tblfanuctrainedparts.partnum, tblfanuctrainedparts.cycletime, tblfanuctrainedparts.partname
)
SELECT 
    partnum, 
    cycletime, 
    partname, 
    SumOfWK_1, 
    SumOfWK_2, 
    SumOfWK_3, 
    SumOfWK_4, 
    SumOfWK_5, 
    SumOfWK_6, 
    SumOfWK_7, 
    SumOfWK_8, 
    (SumOfWK_1 + SumOfWK_2) AS TotalRecvd 
FROM aggregated_data

这两种方式都能正确计算出TotalRecvd的值，你可以根据自己的MySQL版本和查询复杂度选择合适的写法。

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