在SQL中计算并查找一周中的第二天

Question

假设我有这样的数据

CustomerID	Trans_date
C001	9月22日01日
C001	22年9月4日
C001	9月14日至22日
C002	22年9月3日
C002	9月1日至22日
C002	18-sep-22
C002	22年9月20日
C003	9月22日02日
C003	22年9月28日
C004	9月22日08日
C004	18-sep-22

您可以在子查询中使用ROW_NUMBER()函数来获取客户的第一个和第二个交易日期，然后对该子查询的结果使用条件MAX窗口函数。

SELECT CustomerID, DATEPART(week,CustTrans) AS Trans_week,
DATEPART(week, MAX(CASE rn WHEN 1 THEN CustTrans END) OVER (PARTITION BY CustomerID)) first,
DATEPART(week, MAX(CASE rn WHEN 2 THEN CustTrans END) OVER (PARTITION BY CustomerID)) second
FROM
(
SELECT *,
ROW_NUMBER() OVER (PARTITION BY CustomerID ORDER BY CustTrans) rn
FROM trydata
) T
ORDER BY CustomerID, Trans_week

请参阅SQL Server上的演示。

正如您在评论中所要求的，如果您想为每个客户只选择一行显示第一周和第二周，请使用以下查询：

SELECT CustomerID,
DATEPART(week, MAX(CASE rn WHEN 1 THEN CustTrans END)) first,
DATEPART(week, MAX(CASE rn WHEN 2 THEN CustTrans END)) second
FROM
(
SELECT *,
ROW_NUMBER() OVER (PARTITION BY CustomerID ORDER BY CustTrans) rn
FROM trydata
) T
WHERE rn <= 2
GROUP BY CustomerID
ORDER BY CustomerID

查看演示。

with cte (RN,CustomerID, FirstWeek,SecondWeek ) as
( SELECT ROW_NUMBER() over(partition by CustomerID  ORDER BY CustomerID  )  RN, CustomerID,FirstWeek, isnull((select TOP 1 (DATEPART(week,CustTrans))   
from trydata c 
where c.CustomerID = SRC.CustomerID AND DATEPART(week,C.CustTrans) > SRC.FirstWeek  
ORDER BY DATEPART(week,C.CustTrans) ),'0')  AS SecondWeek 
FROM (  
SELECT CustomerID,DATEPART(week,CustTrans) TransWeek,
(select MIN(DATEPART(week,CustTrans)) from trydata c where c.CustomerID = trydata.CustomerID) AS FirstWeek
FROM trydata
) SRC )
select CustomerID,FirstWeek,SecondWeek from cte where RN = 1 

输出：

示例2:

WITH CTE (CustomerID,FIrstWeek,RN) AS (
SELECT CustomerID,MIN(DATEPART(week,CustTrans)) TransWeek,
ROW_NUMBER() over(partition by CustomerID  ORDER BY DATEPART(week,CustTrans) asc ) FROM TryData 
GROUP BY CustomerID,DATEPART(week,CustTrans)
) 
SELECT CTE.CustomerID, CTE.FIrstWeek,  
(select TOP 1 (DATEPART(week,c.CustTrans))   
from trydata c 
where c.CustomerID = CTE.CustomerID AND DATEPART(week,C.CustTrans) > CTE.FIrstWeek 
)   SecondWeek 
FROM CTE  
WHERE RN = 1

FIddle演示

编辑：这可以用更简单、更不复杂的方式完成。