php mysql SELECT AS with WHERE Column not found？

Question

我正在尝试在 SELECT AS 的 mysql PHP PDO 中使用 WHERE 条件，我得到了错误

SQLSTATE[42S22]: Column not found: 1054 Unknown column 'total_consumers' in 'where clause'null

我的查询:

SELECT category.* , SUM(Consumer.capacity) AS total_consumers
FROM company AS company
RIGHT JOIN company AS Consumer ON ( Consumer.category_id = company.category_id AND Consumer.company_type = 'Consumer'  )
RIGHT JOIN category AS category ON ( category.category_id = company.category_id  )
WHERE total_consumers > 0 
GROUP BY category.category_title

目标:

我想获取所有记录inc category table，并且它们应该作为consumer和producer存在于company表中，如果consumer为null则不要选择它

这里是上面查询的json结果

如果我删除 WHERE 条件，我会得到以下 JSON 响应

http://json.live/166EaR

如您所见，某些记录具有不应选择的 total_consumers : null

知道如何做我的观点:(为什么我不能在 WHERE 语句中使用 SELECT AS)

WHERE total_consumers >  
or
WHERE total_consumers != null
or

WHERE xx NOT NULL

Answer 1

您不能在 where 子句中使用来自 select 的别名。您必须使用 having 子句:

SELECT category.* , SUM(Consumer.capacity) AS total_consumers
FROM company AS company
RIGHT JOIN company AS Consumer ON ( Consumer.category_id = company.category_id AND Consumer.company_type = 'Consumer'  )
RIGHT JOIN category AS category ON ( category.category_id = company.category_id  )
GROUP BY category.category_title
having total_consumers > 0