连续3个月发生的TSQL查找顺序

小编典典

连续3个月发生的TSQL查找顺序

sql

请帮助我生成以下查询。说我有客户表和订单表。

客户表

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进行查询）。

所需的输出是：

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

阅读 200

2021-03-23

共1个答案

小编典典

编辑： 摆脱或 MAX() OVER (PARTITION BY ...)那样似乎会削弱性能。

;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

2021-03-23