WHERE子句使用GOALS_MIN列报1054错误，如何正确过滤该列？

错误信息 #

ERROR 1054 (42S22): Unknown column 'GOALS_MIN' in 'where clause'

查询结果结构 #

+-----------------------+-------+-----------+-----------+
| TEAM                  | GOALS | GOALS_MAX | GOALS_MIN |
+-----------------------+-------+-----------+-----------+

原查询语句 #

SET @TOTAL := (select count(id) from matches);
SELECT
  TEAM,
  sum(RESULT) as GOALS,
  max(RESULT) as GOALS_MAX,
  min(RESULT) as GOALS_MIN
FROM
  (SELECT HOME as TEAM,
    CASE WHEN RESULT_HOME > RESULT_VISIT THEN 1 ELSE 0 END as WIN,
    CASE WHEN RESULT_HOME = RESULT_VISIT THEN 1 ELSE 0 END as DRAW,
    CASE WHEN RESULT_HOME < RESULT_VISIT THEN 1 ELSE 0 END as LOSS, RESULT_HOME as RESULT
  FROM matches
  UNION ALL
 SELECT VISIT as TEAM,
    CASE WHEN RESULT_VISIT > RESULT_HOME THEN 1 ELSE 0 END as WIN,
    CASE WHEN RESULT_VISIT = RESULT_HOME THEN 1 ELSE 0 END as DRAW,
    CASE WHEN RESULT_VISIT < RESULT_HOME THEN 1 ELSE 0 END as LOSS, RESULT_VISIT as RESULT
  FROM matches
) as t 
WHERE TEAM LIKE '%TEAM1%' OR TEAM LIKE '%TEAM1%' AND GOALS_MIN >= '2'
GROUP BY TEAM
ORDER BY WIN DESC;

问题原因 #

GOALS_MIN是通过聚合函数min(RESULT)生成的列，属于分组聚合后的结果。而WHERE子句的执行顺序在分组聚合之前，此时该列尚未生成，因此会报“未知列”的错误。

修改方案 #

要过滤聚合后的结果，需要使用HAVING子句替代WHERE中的聚合条件，同时修正原语句中的其他问题：

1. 简化重复的TEAM LIKE '%TEAM1%'条件
2. 去掉数值条件>= '2'的单引号（GOALS_MIN是数值类型）
3. 修正ORDER BY WIN的错误（分组后需聚合WIN列再排序）

修改后的查询语句 #

SET @TOTAL := (select count(id) from matches);
SELECT
  TEAM,
  sum(RESULT) as GOALS,
  max(RESULT) as GOALS_MAX,
  min(RESULT) as GOALS_MIN,
  SUM(WIN) as TOTAL_WINS,
  SUM(DRAW) as TOTAL_DRAWS,
  SUM(LOSS) as TOTAL_LOSSES
FROM
  (SELECT HOME as TEAM,
    CASE WHEN RESULT_HOME > RESULT_VISIT THEN 1 ELSE 0 END as WIN,
    CASE WHEN RESULT_HOME = RESULT_VISIT THEN 1 ELSE 0 END as DRAW,
    CASE WHEN RESULT_HOME < RESULT_VISIT THEN 1 ELSE 0 END as LOSS, RESULT_HOME as RESULT
  FROM matches
  UNION ALL
 SELECT VISIT as TEAM,
    CASE WHEN RESULT_VISIT > RESULT_HOME THEN 1 ELSE 0 END as WIN,
    CASE WHEN RESULT_VISIT = RESULT_HOME THEN 1 ELSE 0 END as DRAW,
    CASE WHEN RESULT_VISIT < RESULT_HOME THEN 1 ELSE 0 END as LOSS, RESULT_VISIT as RESULT
  FROM matches
) as t 
WHERE TEAM LIKE '%TEAM1%'
GROUP BY TEAM
HAVING GOALS_MIN >= 2 -- MySQL 5.7+支持直接用别名，也可写min(RESULT) >= 2
ORDER BY TOTAL_WINS DESC;

补充说明 #

• HAVING子句在分组聚合后执行，因此可以直接引用聚合函数或其别名
• 原语句中ORDER BY WIN会报错，因为WIN是子查询的列，分组后需要通过SUM(WIN)计算总胜场，再基于该聚合结果排序

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