SQL WHERE-Clause 中的聚合函数
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/6319183/
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
Aggregate function in SQL WHERE-Clause
提问by n3on
In a test at university there was a question; is it possible to use an aggregate function in the SQL WHEREclause.
在大学的一次考试中，有一道题；是否可以在SQL WHERE子句中使用聚合函数。
I always thought this isn't possible and I also can't find any example how it would be possible. But my answer was marked false and now I want to know in which cases it is possible to use an aggregate function in the WHERE. Also if it isn't possible it would be nice to get a link to the specification where it is described.
我一直认为这是不可能的，我也找不到任何可能的例子。但是我的答案被标记为错误，现在我想知道在哪些情况下可以在WHERE. 此外，如果不可能的话，最好能得到一个描述它的规范的链接。
采纳答案by Tim Schmelter
You haven't mentioned the DBMS. Assuming you are using MS SQL-Server, I've found a T-SQL Error message that is self-explanatory:
您还没有提到 DBMS。假设您使用的是 MS SQL-Server，我发现了一条不言自明的 T-SQL 错误消息：
"An aggregate may not appear in the WHERE clause unless it is in a subquery contained in a HAVING clause or a select list, and the column being aggregated is an outer reference"
“聚合可能不会出现在 WHERE 子句中，除非它位于包含在 HAVING 子句或选择列表中的子查询中，并且被聚合的列是外部引用”
http://www.sql-server-performance.com/
http://www.sql-server-performance.com/
And an example that it is possible in a subquery.
以及一个可以在子查询中使用的示例。
Show all customers and smallest order for those who have 5 or more orders (and NULL for others):
为那些有 5 个或更多订单的人显示所有客户和最小订单（其他人为 NULL）：
SELECT a.lastname
, a.firstname
, ( SELECT MIN( o.amount )
FROM orders o
WHERE a.customerid = o.customerid
AND COUNT( a.customerid ) >= 5
)
AS smallestOrderAmount
FROM account a
GROUP BY a.customerid
, a.lastname
, a.firstname ;
UPDATE.
更新。
The above runs in both SQL-Server and MySQL but it doesn't return the result I expected. The next one is more close. I guess it has to do with that the field customerid, GROUPed BY and used in the query-subquery join is in the first case PRIMARY KEY of the outer table and in the second case it's not.
以上在 SQL-Server 和 MySQL 中运行，但它没有返回我预期的结果。下一个更接近。我想这与customerid在查询-子查询连接中使用的字段GROUPed BY 在第一种情况下是外表的 PRIMARY KEY 而在第二种情况下不是有关。
Show all customer ids and number of orders for those who have 5 or more orders (and NULL for others):
显示有 5 个或更多订单的客户的所有客户 ID 和订单数量（其他人为 NULL）：
SELECT o.customerid
, ( SELECT COUNT( o.customerid )
FROM account a
WHERE a.customerid = o.customerid
AND COUNT( o.customerid ) >= 5
)
AS cnt
FROM orders o
GROUP BY o.customerid ;
回答by Jason Goemaat
HAVING is like WHERE with aggregate functions, or you could use a subquery.
HAVING 就像带有聚合函数的 WHERE，或者您可以使用子查询。
select EmployeeId, sum(amount)
from Sales
group by Employee
having sum(amount) > 20000
Or
或者
select EmployeeId, sum(amount)
from Sales
group by Employee
where EmployeeId in (
select max(EmployeeId) from Employees)
回答by Jonathan Leffler
You can't use an aggregate directly in a WHERE clause; that's what HAVING clauses are for.
不能在 WHERE 子句中直接使用聚合；这就是 HAVING 子句的用途。
You can use a sub-query which contains an aggregate in the WHERE clause.
您可以使用在 WHERE 子句中包含聚合的子查询。
回答by Chandranshu
UPDATED query:
更新的查询：
select id from t where id < (select max(id) from t);
It'll select all but the last row from the table t.
它将从表 t 中选择除最后一行之外的所有行。
回答by Samir Lakhani
SELECT COUNT( * )
FROM agents
HAVING COUNT(*)>3;
See more below link:
请参阅以下链接：
回答by shaijut
Another solution is to Move the aggregate fuction to Scalar User Defined Function
另一种解决方案是将聚合函数移动到标量用户定义函数
Create Your Function:
创建你的函数：
CREATE FUNCTION getTotalSalesByProduct(@ProductName VARCHAR(500))
RETURNS INT
AS
BEGIN
DECLARE @TotalAmount INT
SET @TotalAmount = (select SUM(SaleAmount) FROM Sales where Product=@ProductName)
RETURN @TotalAmount
END
Use Function in Where Clause
在 Where 子句中使用函数
SELECT ProductName, SUM(SaleAmount) AS TotalSales
FROM Sales
WHERE dbo.getTotalSalesByProduct(ProductName) > 1000
GROUP BY Product
References:
参考：
Hope helps someone.
希望能帮助某人。
