在WHERE from LEFT JOIN中使用的SQL SUM

Question

这是我的SQL代码

SELECT d.*,
SUM(k.active) AS sumactive
FROM devices AS d
LEFT JOIN devicekits AS dk ON dk.id_device = d.id
LEFT JOIN kits AS k ON k.id = dk.id_kit
WHERE sumactive = 0
GROUP BY d.id;

但如果我这样做，我会得到这个错误：

#1054 - Unknown column 'sumactive' in 'where clause'

为什么我无法访问Where子句中的sumactive？

如果有一种方法可以让拉拉威尔关系变得更容易，请继续。

Answer 1

使用

SELECT d.*, SUM(k.active) as sumactive 
FROM devices AS d 
LEFT JOIN devicekits AS dk ON dk.id_device = d.id 
LEFT JOIN kits as k ON k.id = dk.id_kit
GROUP BY d.id
HAVING sumactive = 0;

错误代码显示的内容非常清楚。别名不能在WHERE中使用。

此外，由于使用了聚合函数，因此需要HAVING。