SQL报错：将两列求和赋值为Rebounds后使用WHERE条件出错

报错原因 #

SQL的执行顺序是先执行WHERE子句过滤行，再执行SELECT子句生成列和别名，所以WHERE里没法直接引用SELECT中定义的Rebounds别名，这就是报错的核心原因。另外原代码还缺少GROUP BY子句——使用SUM这类聚合函数时，必须指定分组字段，否则会触发语法错误。

解决方案 #

方案1：将计算逻辑直接写入WHERE子句

把Rebounds的计算逻辑（offreb + defreb）直接放到WHERE里，同时补上必要的GROUP BY：

SELECT team, CONCAT(firstname,' ',lastname) AS name, SUM(offreb + defreb) AS Rebounds
FROM boxscore
WHERE round = 'Finals' AND game = 7 AND (offreb + defreb) > 0
GROUP BY team, firstname, lastname
ORDER BY team, Rebounds;

方案2：用子查询先计算聚合值再过滤

先通过子查询生成包含Rebounds的结果集，再在外层WHERE中过滤：

SELECT team, name, Rebounds
FROM (
    SELECT team, CONCAT(firstname,' ',lastname) AS name, SUM(offreb + defreb) AS Rebounds
    FROM boxscore
    WHERE round = 'Finals' AND game = 7
    GROUP BY team, firstname, lastname
) AS sub_query
WHERE Rebounds > 0
ORDER BY team, Rebounds;

方案3：使用CTE（公共表表达式，适用于MySQL 8+、PostgreSQL等）

用CTE先定义聚合结果，再进行过滤，代码可读性更高：

WITH rebound_stats AS (
    SELECT team, CONCAT(firstname,' ',lastname) AS name, SUM(offreb + defreb) AS Rebounds
    FROM boxscore
    WHERE round = 'Finals' AND game = 7
    GROUP BY team, firstname, lastname
)
SELECT *
FROM rebound_stats
WHERE Rebounds > 0
ORDER BY team, Rebounds;

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