SQL SERVER：如何使用像MAX聚合函数在WHERE子句(Sql Server : How t

我希望得到这个记录的最大值。 请帮我：

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

Answer 1:

你可以使用一个子查询...

WHERE t1.field3 = (SELECT MAX(st1.field3) FROM table1 AS st1)

但我实际上把这个出where子句，进入连接语句，作为和ON子句。

Answer 2:

正如你已经注意到，在WHERE子句中不允许你在使用它的聚集。 那是什么HAVING子句的。

HAVING t1.field3=MAX(t1.field3)

Answer 3:

在having子句中使用最多的正确方法是通过加入第一执行自：

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)

下面是你将如何使用子查询联接：

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)

请务必使用具有多表打交道时加入一个聚合之前创建一个单一的数据集：

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

子查询的版本：

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)

Answer 4:

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)

Answer 5:

是的，你需要通过子句中使用having子句集团后，由于这里只是紧跟着具有语句来过滤的简单参数的数据，但集团的理念是对数据进行分组和过滤它的一些汇总的基础功能......

Answer 6:

但它仍然给查询生成器的错误信息。 我使用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))

我与具有由@powerlord说WHERE取代。 但仍然显示错误。

错误分析查询。 [令牌行号= 1，令牌行偏移= 371，令牌在错误= SELECT]

文章来源: Sql Server : How to use an aggregate function like MAX in a WHERE clause