mysql - GROUP BY + SUM 在 where 子句中

Question

我运行了这个查询。

SELECT 
   grupo, 
   solicitante, 
   sum(valuacion) as valuacion, 
   fecha, 
   count(*) as cant, 
   sum(aprobado) as ap, 
   sum(goe) as goe 
FROM 
   presupuestos
WHERE 
   ap=0 AND 
   goe=cant AND 
   grupo>0 AND 
   elim=0 AND 
   sup=0 
GROUP BY grupo 
ORDER BY fecha_aprobacion ASC

MySQL 返回:

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

因为“ap”来自sum，不是行列。

对此有任何 SQL 解决方法吗？

Answer 1

您不能在 WHERE 子句中引用别名，因为查询的选择部分是最后执行的，并且尚未创建别名。您也不能在 WHERE 中使用聚合函数，您应该改用 HAVING:

SELECT 
   grupo, 
   solicitante, 
   sum(valuacion) as valuacion, 
   fecha, 
   count(*) as cant, 
   sum(aprobado) as ap, 
   sum(goe) as goe 
FROM 
   presupuestos
WHERE 
   grupo>0 AND 
   elim=0 AND 
   aprobado=0 
GROUP BY grupo,solicitante,fecha
HAVING  sum(aprobado)=0 AND sum(goe)= count(*) AND sum(goe)=1
ORDER BY fecha_aprobacion ASC