如何在SQL Server中筛选首周为35且次周为37的查询结果

原查询语句 #

WITH CTE (customerID,FirstWeek,RN) AS 
(
    SELECT 
        customerID, MIN(DATEPART(week, tp_date)) TransWeek,
        ROW_NUMBER() OVER (PARTITION BY customerID ORDER BY DATEPART(week, tp_date) ASC) 
    FROM 
        all_table
    GROUP BY 
        customerID, DATEPART(week, tp_date)
) 
SELECT 
    CTE.customerID, CTE.FirstWeek,  
    (SELECT TOP 1 (DATEPART(week, c.tp_date))   
     FROM all_table c 
     WHERE c.customerID = CTE.customerID 
       AND DATEPART(week, c.tp_date) > CTE.FirstWeek) SecondWeek 
FROM 
    CTE  
WHERE 
    RN = 1

原查询结果 #

期望结果 #

修改后的查询语句 #

只需在原查询的外层WHERE子句中添加筛选条件，即可得到期望结果：

WITH CTE (customerID,FirstWeek,RN) AS 
(
    SELECT 
        customerID, MIN(DATEPART(week, tp_date)) TransWeek,
        ROW_NUMBER() OVER (PARTITION BY customerID ORDER BY DATEPART(week, tp_date) ASC) 
    FROM 
        all_table
    GROUP BY 
        customerID, DATEPART(week, tp_date)
) 
SELECT 
    CTE.customerID, CTE.FirstWeek,  
    (SELECT TOP 1 (DATEPART(week, c.tp_date))   
     FROM all_table c 
     WHERE c.customerID = CTE.customerID 
       AND DATEPART(week, c.tp_date) > CTE.FirstWeek) SecondWeek 
FROM 
    CTE  
WHERE 
    RN = 1
    AND FirstWeek = 35
    AND SecondWeek = 37

说明 #

通过在原查询的外层筛选条件中增加FirstWeek = 35和SecondWeek = 37，可以直接过滤出符合要求的记录，同时保留原查询中RN = 1的条件，确保只获取每个客户的最早交易周数据。

内容的提问来源于stack exchange，提问作者tasya fauzia fitriasari