SQL：聚合后如何过滤？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/15767794/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
SQL: How to filter after aggregation?
提问by Stumbler
It is very easy to remove values that you don't want aggregated.
删除不想聚合的值非常容易。
For instance:
例如：
SELECT department, SUM(sales) as "Total sales"
FROM order_details
GROUP BY department
HAVING SUM(sales) > 1000;
Which will exclude all sales with a value less than or equal to 1000 from the summing aggregation.
这将从求和聚合中排除所有值小于或等于 1000 的销售额。
But how do you filter after the aggregation?
但是聚合后如何过滤呢？
E.g. WHERE ("Total sales"> 15000)
例如 WHERE ("Total sales"> 15000)
Edit: Ironically I was only including HAVING SUM(sales) > 1000;in order to prevent confusion about the type of query required; because I'm not actually interested in excluding items from the summing, just the returned results! Thanks, despite confusion!
编辑：具有讽刺意味的是，我只是HAVING SUM(sales) > 1000;为了防止混淆所需的查询类型；因为我实际上对从求和中排除项目不感兴趣，只对返回的结果感兴趣！谢谢，尽管很困惑！
回答by jurgenreza
The query you have is actually doing what you want and not what you expressed in the question. If you want to exclude all sales with a value less than 1000, you should use WHERE sales > 1000. But with HAVING SUM(sales) > 1000the filtering is actually done after the aggregation.
您的查询实际上是在做您想做的事情，而不是您在问题中表达的内容。如果要排除值小于 1000 的所有销售额，则应使用WHERE sales > 1000. 但是HAVING SUM(sales) > 1000过滤实际上是在聚合之后完成的。
Writing a sub-query and adding another SELECT WHEREon top of the original query is redundant.
编写一个子查询并SELECT WHERE在原始查询之上添加另一个是多余的。
Please see fiddlefor clarification.
请参阅小提琴以进行澄清。
#Query1
SELECT department, SUM(sales) as Total
FROM order_details
GROUP BY department
HAVING Total > 40;
#Query 2
SELECT department, SUM(sales) as Total
FROM order_details
GROUP BY department
HAVING SUM(sales) > 40;
#Query 3
SELECT department, SUM(sales) as Total
FROM order_details
WHERE sales > 40
GROUP BY department;
#Query 1 and 2 are the same, filtering after aggregation
#Query 3 is filtering before aggregation
回答by Cyril Gandon
If you want to filter the sale with value less than 1000, the right query is
如果要过滤值小于 1000 的销售，正确的查询是
SELECT department, sales
FROM order_details
WHERE sales > 1000
If you want to aggregate, and keep only the sum be more than 15000, then you need this query :
如果你想聚合，并且只保持总和大于 15000，那么你需要这个查询：
SELECT department, SUM(sales) as TotalSales
FROM order_details
WHERE sales > 1000
GROUP BY department
HAVING SUM(sales) > 15000
回答by techturtle
You can do it as a two-step process by selecting the sum into a temp table, then selecting from the temp table:
您可以通过将总和选择到临时表中，然后从临时表中进行选择，分两步完成：
SELECT department, SUM(sales) as TotalSales
INTO #temp
FROM order_details
GROUP BY department
HAVING SUM(sales) > 1000
SELECT department, TotalSales
FROM #temp
WHERE TotalSales > 15000
DROP TABLE #temp
