Sql Server：如何在 WHERE 子句中使用像 MAX 这样的聚合函数
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1475589/
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 Server : How to use an aggregate function like MAX in a WHERE clause
提问by Powerlord
I want get the maximum value for this record. Please help me:
我想获得此记录的最大值。请帮我：
SELECT rest.field1
FROM mastertable AS m
INNER JOIN (
SELECT t1.field1 field1,
t2.field2
FROM table1 AS T1
INNER JOIN table2 AS t2 ON t2.field = t1.field
WHERE t1.field3=MAX(t1.field3)
-- ^^^^^^^^^^^^^^ Help me here.
) AS rest ON rest.field1 = m.field
采纳答案by dlamblin
You could use a sub query...
您可以使用子查询...
WHERE t1.field3 = (SELECT MAX(st1.field3) FROM table1 AS st1)
But I would actually move this out of the where clause and into the join statement, as an AND for the ON clause.
但我实际上会将它从 where 子句移到 join 语句中，作为 ON 子句的 AND。
回答by Powerlord
As you've noticed, the WHEREclause doesn't allow you to use aggregates in it. That's what the HAVINGclause is for.
正如您所注意到的，该WHERE子句不允许您在其中使用聚合。这就是该HAVING条款的用途。
HAVING t1.field3=MAX(t1.field3)
回答by metrix
The correct way to use max in the having clause is by performing a self join first:
在 having 子句中使用 max 的正确方法是先执行自连接：
select t1.a, t1.b, t1.c
from table1 t1
join table1 t1_max
on t1.id = t1_max.id
group by t1.a, t1.b, t1.c
having t1.date = max(t1_max.date)
The following is how you would join with a subquery:
以下是您将如何加入子查询：
select t1.a, t1.b, t1.c
from table1 t1
where t1.date = (select max(t1_max.date)
from table1 t1_max
where t1.id = t1_max.id)
Be sure to create a single dataset before using an aggregate when dealing with a multi-table join:
在处理多表连接时，请确保在使用聚合之前创建单个数据集：
select t1.id, t1.date, t1.a, t1.b, t1.c
into #dataset
from table1 t1
join table2 t2
on t1.id = t2.id
join table2 t3
on t1.id = t3.id
select a, b, c
from #dataset d
join #dataset d_max
on d.id = d_max.id
having d.date = max(d_max.date)
group by a, b, c
Sub query version:
子查询版本：
select t1.id, t1.date, t1.a, t1.b, t1.c
into #dataset
from table1 t1
join table2 t2
on t1.id = t2.id
join table2 t3
on t1.id = t3.id
select a, b, c
from #dataset d
where d.date = (select max(d_max.date)
from #dataset d_max
where d.id = d_max.id)
回答by Tim Santeford
SELECT rest.field1
FROM mastertable as m
INNER JOIN table1 at t1 on t1.field1 = m.field
INNER JOIN table2 at t2 on t2.field = t1.field
WHERE t1.field3 = (SELECT MAX(field3) FROM table1)
回答by Rohit
yes you need to use a having clause after the Group by clause , as the where is just to filter the data on simple parameters , but group by followed by a Having statement is the idea to group the data and filter it on basis of some aggregate function......
是的，您需要在 Group by 子句之后使用一个ifying 子句，因为 where 只是过滤简单参数上的数据，但是 group by 后跟一个 Had 语句是对数据进行分组并根据某些聚合对其进行过滤的想法功能......
回答by Kamal
But its still giving an error message in Query Builder. I am using SqlServerCe 2008.
但它仍然在查询生成器中给出错误消息。我正在使用 SqlServerCe 2008。
SELECT Products_Master.ProductName, Order_Products.Quantity, Order_Details.TotalTax, Order_Products.Cost, Order_Details.Discount,
Order_Details.TotalPrice
FROM Order_Products INNER JOIN
Order_Details ON Order_Details.OrderID = Order_Products.OrderID INNER JOIN
Products_Master ON Products_Master.ProductCode = Order_Products.ProductCode
HAVING (Order_Details.OrderID = (SELECT MAX(OrderID) AS Expr1 FROM Order_Details AS mx1))
I replaced WHERE with HAVING as said by @powerlord. But still showing an error.
正如@powerlord 所说，我用 HAVING 替换了 WHERE。但仍然显示错误。
Error parsing the query. [Token line number = 1, Token line offset = 371, Token in error = SELECT]
解析查询时出错。[令牌行号= 1，令牌行偏移= 371，令牌错误= SELECT]
