SQL - 一个未定义的区间内查询记录

Question

我需要使用SQL（SQL Server来准确）从Orders表得到Northwind数据库中的以下数据：

• 找到所有的客户（CustomerID）谁把至少 内的3个订单的间隔为6个月（OrderDate）。

因此以下数据的查询应返回1

========================== 
| CustomerID | OrderDate | 
========================== 
|  1  | 2000-1-1 | 
-------------------------- 
|  1  | 2000-1-5 | 
-------------------------- 
|  1  | 2000-4-30 | 
-------------------------- 
|  2  | 2000-1-1 | 
-------------------------- 
|  2  | 2000-5-30 | 
-------------------------- 

Answer 1

SELECT N1.CustomerID, COUNT(N2.OrderDate) FROM Northwind N1 
INNER JOIN Northwind N2 
ON N1.CustomerID=N2.CustomerID 
AND N1.OrderDate<=Dateadd(MONTH, 6, N2.OrderDate) 
GROUP BY N1.CustomerID 
HAVING COUNT(N2.OrderDate)>=3 

Answer 2

尝试以下操作：

SELECT OrderDate, CustomerID FROM orders GROUP BY OrderDate HAVING COUNT(CustomerID) >= 3 ORDER BY COUNT(CustomerID) DESC