有没有办法转换客户 ID 及其最近的订单日期？

发布于 2024-09-12 13:06:39 字数 707 浏览 3 评论 0原文

我有一个查询，它为我提供了所有客户及其最后三个订单日期。

EX：

CustomerId DateOrdered
167 2006-09-16 01:25:38.060
167 2006-09-21 13:11:53.530
171 2006-08-31 15:19:22.543
171 2006-09-01 13:30:54.013
171 2006-09-01 13:34:36.483
178 2006-09-04 11:36:19.983
186 2006-09-05 12:50:27.153
186 2006-09-05 12:51:08.513

我想知道是否有办法将其旋转为如下显示：

[CustomerId]    [Most Recent] [Middle] [Oldest]
'167'   '2006-09-21 13:11:53.530'   '2006-09-16 01:25:38.060'   'NULL'
'171'    '2006-09-01 13:34:36.483'   '2006-09-01 13:30:54.013'   '2006-08-31 15:19:22.543'
'178'   '2006-09-04 11:36:19.983'   NULL    NULL
'186'   '2006-09-05 12:51:08.513'   '2006-09-05 12:50:27.153'   NULL

原文

I have a query that gives me all customer's and their last three order dates.

EX:

CustomerId DateOrdered
167 2006-09-16 01:25:38.060
167 2006-09-21 13:11:53.530
171 2006-08-31 15:19:22.543
171 2006-09-01 13:30:54.013
171 2006-09-01 13:34:36.483
178 2006-09-04 11:36:19.983
186 2006-09-05 12:50:27.153
186 2006-09-05 12:51:08.513

I want to know if there is a way for me to pivot it to display like this:

[CustomerId]    [Most Recent] [Middle] [Oldest]
'167'   '2006-09-21 13:11:53.530'   '2006-09-16 01:25:38.060'   'NULL'
'171'    '2006-09-01 13:34:36.483'   '2006-09-01 13:30:54.013'   '2006-08-31 15:19:22.543'
'178'   '2006-09-04 11:36:19.983'   NULL    NULL
'186'   '2006-09-05 12:51:08.513'   '2006-09-05 12:50:27.153'   NULL

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

恏ㄋ傷疤忘ㄋ疼 2024-09-19 13:06:39

;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]

;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]

回复收藏 0 原文

时光与爱终年不遇 2024-09-19 13:06:39

警告：未经测试

我认为您正在寻找类似的东西。特定的联接和 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)

Warning: Not Tested

I think you're looking for something like this. The specific joins and where clause may need some work, but basically your just joining the table back on itself to get one date each time.

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)

回复收藏 0 原文

~没有更多了~