编辑： 摆脱或
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 cte1GROUP BY CustID, GHAVINg MAX(YM)-MIN(YM) >=2 )SELECt c.CustName, o.OrderDate, YEAR(o.OrderDate) AS YEARFROM Customers AS c INNER JOINOrders AS o ON c.CustID = o.CustIDINNER JOIN cte2 c2 ON c2.CustID = o.CustID and o.OrderDate between Mn and Mxorder by c.CustName, o.OrderDate
