在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子句中引用别名，因为查询的select部分是最后执行的，而别名尚未生成。您也不能在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

Answer 2

你必须在“拥有”条款中提及这一点。

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 
   sup=0 AND 
   goe=1 AND 
   aprobado=0 
GROUP BY grupo 
HAVING
   sum(aprobado) = 0 and
   count(*) = sum(goe)
ORDER BY 
   fecha_aprobacion ASC