如何在SQL中通过聚合字段筛选(where子句)

我有以下查询：

 Select Distinct Product, sum(Deals) as TotalDeals
 from Metrics m
 group by Product

并且我想将查询更改为仅返回TotalDeals=0的位置。

在查询中使用where子句的最佳方式是什么（而不是在事实发生后从结果集中过滤出来）

Select Product, TotalDeals
FROM
(
 Select Product, sum(Deals) as TotalDeals
 from Metrics m
 group by Product
)
Where TotalDeals=0

您不需要区分，因为记录是按产品分组的

或

Select Product, sum(Deals) as TotalDeals
from Metrics m
group by Product
having sum(Deals) = 0

尝试使用HAVING:

Select Distinct Product, sum(Deals) as TotalDeals 
from Metrics m 
group by Product 
HAVING sum(Deals) = 0

HAVING是筛选聚合字段的子句

   ...
   group by Product
   HAVING sum(Deals) = 0

  Select Product, sum(Deals) as TotalDeals
     from Metrics m
     group by Product
    having sum(Deals)=0

要使其在MySQL数据库中工作，而不会收到错误代码1248：每个派生表都必须有自己的别名

此查询：

Select Product, TotalDeals
FROM
(
 Select Product, sum(Deals) as TotalDeals
 from Metrics m
 group by Product
)
Where TotalDeals=0

必须更新以在右括号后包含AS别名，如下所示：

Select Product, TotalDeals
FROM
(
 Select Product, sum(Deals) as TotalDeals
 from Metrics m
 group by Product
) AS t1
Where TotalDeals=0

相关内容