在一年没有记录之后，创造了新的记录

Question

我想找到在一年没有订单后下订单的回头客。我已经做到了以下几点，但很难再加上一年的差距。类似于"；并且日期之间的订单计数＝0"；。。。任何想法都将不胜感激，我似乎根本无法理解所需的语法。

SELECT
Min(Orders.[Order Date]) AS [MinOfOrder Date],
Max(Orders.[Order Date]) AS [MaxOfOrder Date],
Orders.CustomerID
FROM
Customers
INNER JOIN Orders ON Customers.CustomerID = Orders.CustomerID
GROUP BY
Orders.CustomerID
HAVING
(
((Min(Orders.[Order Date])) < Date() -365)
AND ((Max(Orders.[Order Date])) > Date() -30)
);

Answer 1

您可以使用exists和not exists来获得一年后的第一个订单：

select o.*
from orders as o
where exists (select 1
from orders as o2
where o2.customerid = o.customerid and
o2.orderdate < dateadd("yyyy", o.orderdate, -1)
) and
not exists (select 1
from orders as o2
where o2.customerid = o.customerid and
o2.orderdate >= dateadd("yyyy", o.orderdate, -1) and
o2.orderdate < o.orderdate
);

如果需要，您可以加入客户信息。

Answer 2

根据其他记录中的值选择记录很棘手。使用相关的子查询从另一条记录中提取值。需要一个唯一的标识符字段-应提供autonumber。考虑：

查询1:

SELECT Orders.*, (SELECT Max(OrderDate) 
FROM Orders AS Dupe 
WHERE Dupe.CustomerID = Orders.CustomerID 
AND Dupe.ID < Orders.ID) AS PrevOrderDate
FROM Orders;

查询2:
SELECT Query1.* FROM Query1 WHERE ((([OrderDate]-[PrevOrderDate])>365));