如何关联dbo.Stuff与dbo.HistPrices获取指定周数的首个历史价格

我有两张SQL表：

1. dbo.HistPrices（存储各PId的历史价格及其他元数据），表结构和数据如下：

2. dbo.Stuff（类似购物车，存储特定商品当前周的价格，Sid对应PId），表结构和数据如下：

注：SId与PId为对应字段但名称不同，dbo.HistPrices包含与dbo.Stuff无关的数据。

需求 #

生成结果表，为dbo.Stuff新增以下字段：

• last_week_Price：至少一周前的最新价格（一周内有多条则取最新的）
• Last_week_PriceDate：对应last_week_Price的日期
• week_before_Price：至少两周前的最新价格
• week_before_date：对应week_before_Price的日期

结果表结构如下：

具体要求 #

• 必须保持dbo.Stuff的行数不变，无匹配数据则填充NULL
• 我已经实现了单个SId的CTE查询，但不知道如何关联两张表实现批量处理所有SId的需求。单个SId的代码如下：

DECLARE @get_Date VARCHAR(100)
DECLARE @SId int
DECLARE @week_offset int
SET @get_Date = 'teststring in date format' 
SET @SId = 12345
SET @week_offset  = -1;

WITH cte AS (
    SELECT *, ROW_NUMBER() OVER (
                  PARTITION BY hp.PId
                  ORDER BY  hp.PriceDate DESC 
--- i also thoguth abut DATEDIFF(week, hp.Pricedate,CONVERT(DATETIME,@get_Date) )
              ) rn
    FROM   dbo.HistPrices hp
    WHERE (hp.Pricedate >= DATEADD(Week, @week_offset,CONVERT(DATETIME,@get_Date))
    AND hp.Pricedate < CONVERT(DATETIME,@get_Date) ) 
     AND hp.PId = @SId
)

SELECT *
FROM cte
WHERE rn = 1
ORDER BY PId

可以通过预先生成所有PId的两周历史价格数据，再与dbo.Stuff做左连接来实现，既保证Stuff的行数不变，又能批量处理所有SId。以下提供两种可行方案：

方案一：双独立子查询（逻辑清晰） #

SELECT 
    s.SId,
    s.Price,
    s.Pricedate,
    s.[desc],
    -- 保留Stuff表的其他字段
    lw.last_week_Price,
    lw.Last_week_PriceDate,
    wb.week_before_Price,
    wb.week_before_date
FROM dbo.Stuff s
LEFT JOIN (
    -- 获取每个PId上周的最新价格（区间：当前日期前1周到前0周）
    SELECT 
        hp.PId,
        hp.Price AS last_week_Price,
        hp.Pricedate AS Last_week_PriceDate
    FROM (
        SELECT 
            *,
            ROW_NUMBER() OVER (PARTITION BY PId ORDER BY Pricedate DESC) rn
        FROM dbo.HistPrices hp
        WHERE 
            hp.Pricedate >= DATEADD(WEEK, -1, s_inner.Pricedate)
            AND hp.Pricedate < s_inner.Pricedate
    ) hp
    WHERE hp.rn = 1
) lw ON s.SId = lw.PId
LEFT JOIN (
    -- 获取每个PId上上周的最新价格（区间：当前日期前2周到前1周）
    SELECT 
        hp.PId,
        hp.Price AS week_before_Price,
        hp.Pricedate AS week_before_date
    FROM (
        SELECT 
            *,
            ROW_NUMBER() OVER (PARTITION BY PId ORDER BY Pricedate DESC) rn
        FROM dbo.HistPrices hp
        WHERE 
            hp.Pricedate >= DATEADD(WEEK, -2, s_inner.Pricedate)
            AND hp.Pricedate < DATEADD(WEEK, -1, s_inner.Pricedate)
    ) hp
    WHERE hp.rn = 1
) wb ON s.SId = wb.PId

方案二：CTE+条件聚合（代码紧凑） #

WITH HistPriceRanked AS (
    SELECT 
        hp.PId,
        hp.Price,
        hp.Pricedate,
        s.Pricedate AS current_date,
        -- 计算历史价格与当前商品价格的周差
        DATEDIFF(WEEK, hp.Pricedate, s.Pricedate) AS week_diff,
        -- 按PId和周差分组，取每组最新的价格
        ROW_NUMBER() OVER (
            PARTITION BY hp.PId, DATEDIFF(WEEK, hp.Pricedate, s.Pricedate)
            ORDER BY hp.Pricedate DESC
        ) rn
    FROM dbo.HistPrices hp
    RIGHT JOIN dbo.Stuff s ON hp.PId = s.SId
)
SELECT 
    s.SId,
    s.Price,
    s.Pricedate,
    s.[desc],
    -- 保留Stuff表的其他字段
    MAX(CASE WHEN hpr.week_diff = 1 THEN hpr.Price END) AS last_week_Price,
    MAX(CASE WHEN hpr.week_diff = 1 THEN hpr.Pricedate END) AS Last_week_PriceDate,
    MAX(CASE WHEN hpr.week_diff = 2 THEN hpr.Price END) AS week_before_Price,
    MAX(CASE WHEN hpr.week_diff = 2 THEN hpr.Pricedate END) AS week_before_date
FROM dbo.Stuff s
LEFT JOIN HistPriceRanked hpr ON s.SId = hpr.PId AND hpr.rn = 1
GROUP BY s.SId, s.Price, s.Pricedate, s.[desc] -- 包含Stuff所有需要保留的字段

方案说明 #

• 两种方案均使用LEFT JOIN，确保dbo.Stuff的所有行被保留，无匹配历史数据时对应字段自动填充NULL；
• ROW_NUMBER()函数保证每个PId在目标时间区间内只取最新的一条价格记录；
• 方案一逻辑直观，适合需要单独调整某一周查询规则的场景；方案二代码更简洁，适合批量处理多周数据的场景。

内容的提问来源于stack exchange，提问作者NorrinRadd