连续3个月发生的TSQL查找顺序 [英] TSQL Finding Order that occurred in 3 consecutive months
本文介绍了连续3个月发生的TSQL查找顺序的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
请帮助我生成以下查询.假设我有客户表和订单表.
Please help me to generate the following query. Say I have customer table and order table.
客户表
CustID CustName
1 AA
2 BB
3 CC
4 DD
订单表
OrderID OrderDate CustID
100 01-JAN-2000 1
101 05-FEB-2000 1
102 10-MAR-2000 1
103 01-NOV-2000 2
104 05-APR-2001 2
105 07-MAR-2002 2
106 01-JUL-2003 1
107 01-SEP-2004 4
108 01-APR-2005 4
109 01-MAY-2006 3
110 05-MAY-2007 1
111 07-JUN-2007 1
112 06-JUL-2007 1
我想找出连续三个月下订单的客户. (允许使用SQL Server 2005和2008进行查询).
I want to find out the customers who have made orders on three successive months. (Query using SQL server 2005 and 2008 is allowed).
所需的输出是:
CustName Year OrderDate
AA 2000 01-JAN-2000
AA 2000 05-FEB-2000
AA 2000 10-MAR-2000
AA 2007 05-MAY-2007
AA 2007 07-JUN-2007
AA 2007 06-JUL-2007
推荐答案
编辑:摆脱或MAX() OVER (PARTITION BY ...)
似乎会降低性能.
Got rid or the MAX() OVER (PARTITION BY ...)
as that seemed to kill performance.
;WITH cte AS (
SELECT CustID ,
OrderDate,
DATEPART(YEAR, OrderDate)*12 + DATEPART(MONTH, OrderDate) AS YM
FROM Orders
),
cte1 AS (
SELECT CustID ,
OrderDate,
YM,
YM - DENSE_RANK() OVER (PARTITION BY CustID ORDER BY YM) AS G
FROM cte
),
cte2 As
(
SELECT CustID ,
MIN(OrderDate) AS Mn,
MAX(OrderDate) AS Mx
FROM cte1
GROUP BY CustID, G
HAVING MAX(YM)-MIN(YM) >=2
)
SELECT c.CustName, o.OrderDate, YEAR(o.OrderDate) AS YEAR
FROM Customers AS c INNER JOIN
Orders AS o ON c.CustID = o.CustID
INNER JOIN cte2 c2 ON c2.CustID = o.CustID and o.OrderDate between Mn and Mx
order by c.CustName, o.OrderDate
这篇关于连续3个月发生的TSQL查找顺序的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文