查找客户订单频率
查找客户订单频率
提问于 2016-12-08 14:46:23
我需要一个查询，给我的结果，为客户相同的生产订单在10天内。由于生产管理，制造商不喜欢客户在短时间内多次订购相同的产品(例如，10天是我的短时间)。
下面是示例订单表代码。
DECLARE @Orders TABLE(Customer VARCHAR(50), Product VARCHAR(50), OrderDate DATE)
INSERT INTO @Orders VALUES
('A Company','w102', '2016-10-10'),('A Company','w102', '2016-10-12'),('A Company','w102', '2016-10-24'),
('B Company','w302', '2016-11-11'),('B Company','w302', '2016-11-15'),('B Company','w302', '2016-11-17'),
('C Company','w404', '2016-11-17'),('C Company','w404', '2016-11-24'),('C Company','w405', '2016-11-24')
select * from @Orders ORDER BY Customer, OrderDate 那么，我怎样才能在10天内得到客户订单清单呢？
回答 1
Stack Overflow用户
回答已采纳
发布于 2016-12-08 14:49:33
您可以简单地使用EXISTS在原始订单的10天内检查同一个客户和产品的另一个订单：
SELECT *
FROM @Orders AS o
WHERE EXISTS
( SELECT 1
FROM @Orders AS o2
WHERE o2.Customer = o.Customer
AND o2.Product = o.Product
AND o2.OrderDate > o.OrderDate
AND o2.OrderDate <= DATEADD(DAY, 10, o.OrderDate)
);这意味着：
Customer Product OrderDate
---------------------------------------
A Company w102 2016-10-10
B Company w302 2016-11-11
B Company w302 2016-11-15
C Company w404 2016-11-17这将只返回两个订单中的第一个订单的详细信息，如果需要下一个订单的详细信息(如日期)，则可以使用CROSS APPLY。
SELECT o.Customer,
o.Product,
FirstOrderDate = o.OrderDate,
NextOrderDate = o2.OrderDate
FROM @Orders AS o
CROSS APPLY
( SELECT TOP 1 o2.OrderDate
FROM @Orders AS o2
WHERE o2.Customer = o.Customer
AND o2.Product = o.Product
AND o2.OrderDate > o.OrderDate
AND o2.OrderDate <= DATEADD(DAY, 10, o.OrderDate)
ORDER BY o2.OrderDate
) AS o2;这意味着：
Customer Product FirstOrderDate NextOrderDate
------------------------------------------------
A Company w102 2016-10-10 2016-10-12
B Company w302 2016-11-11 2016-11-15
B Company w302 2016-11-15 2016-11-17
C Company w404 2016-11-17 2016-11-24第三个版本(仅在Server 2012及更高版本中提供)是使用LEAD()或LAG()
SELECT o.Customer,
o.Product,
o.FirstOrderDate,
o.NextOrderDate
FROM ( SELECT o.Customer,
o.Product,
FirstOrderDate = o.OrderDate,
NextOrderDate = LEAD(o2.OrderDate) OVER(PARTITION BY Customer, Product ORDER BY o.OrderDate)
FROM @Orders AS o
WHERE o2.Customer = o.Customer
) AS o
WHERE o.NextOrderDate > o.FirstOrderDate
AND o.NextOrderDate <= DATEADD(DAY, 10, o.FirstOrderDate);页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/41042165
复制相关文章
相似问题
腾讯云开发者
