有没有方法可以透视客户ID和他们最近的订单日期?

;WITH YourQuery As
(
SELECT CustomerId, DateOrdered,
ROW_NUMBER() OVER (PARTITION BY CustomerId ORDER BY DateOrdered DESC) AS RN
FROM Orders
)
select [CustomerId],
MAX(CASE WHEN RN=1 THEN DateOrdered END) AS  [Most Recent] ,
MAX(CASE WHEN RN=2 THEN DateOrdered END) AS  [Middle] ,
MAX(CASE WHEN RN=3 THEN DateOrdered END) AS  [Oldest] 
FROM YourQuery
WHERE RN<=3
GROUP BY [CustomerId]

警告:未测试

我想你在找这样的东西。特定的join和where子句可能需要一些工作,但基本上,您只是将表重新连接起来,以便每次获得一个日期。

SELECT C.CUSTOMERID, C.DATEORDERED, C2.DATEORDERED, C3.DATEORDERED
FROM CUSTOMER C
INNER JOIN CUSTOMER C2 ON C.CUSTOMERID = C2.CUSTOMERID
INNER JOIN CUSTOMER C3 ON C.CUSTOMERID = C3.CUSTOMERID
WHERE C.DATEORDERED = MAX(C.DATEORDERED)
AND C2.DATEORDERED < C.DATEORDERED AND 
    (C3.DATEORDERED IS NULL OR C2.DATEORDERED >     C3.DATEORDERED)