复杂日期谓词用法

Question

我正试图根据表中最长日期和最长日期前20天窗口之间的记录来筛选结果。

我想出了以下代码：-

SELECT orderid, custid, empid, shipperid, orderdate, filler
FROM dbo.Orders
WHERE orderid BETWEEN min(max(orderdate)-DATEADD(day,20,orderdate)) AND max(orderdate)
order by orderid

这会产生以下错误：-

Msg 130, Level 15, State 1, Line 226
Cannot perform an aggregate function on an expression containing an aggregate or a subquery.

我不明白为什么。请有人告诉我为什么会出现这个错误，并建议我如何实现目标？

Answer 1

不能在where子句中使用聚合函数。可能最好的方法是将逻辑放入from:

select o.*
from dbo.Orders o cross join
     (select max(orderdate) as maxorderdate from orders) omax
where o.orderdate between dateadd(day, -20, omax.maxorderdate) and omax.maxorderdate
order by orderid;

编辑：

另一种方法是：

select o.*
from (select o.*, max(orderdate) over () as maxorderdate
      from dbo.orders o
     ) o
where orderdate between dateadd(day, -20, maxorderdate) and maxorderdate
order by orderid;

以前的版本可能更高效，但值得测试这两个版本。