基于指定日期获取各门店最新广告费率的SQL查询问题

需求说明 #

• 传入参数@ToDate，筛选广告费率日期（AdRateDate）不晚于该值的记录
• 获取每个门店（StoreNumber）的最新广告费率，返回包含门店编号、广告费率等多列数据

原代码问题分析 #

原SQL中子查询的StoreNumber = StoreNumber属于无关联的自引用，会计算整个表中符合日期条件的最大AdRateDate，而非每个门店单独的最大日期，导致查询结果不符合预期。

正确实现方案 #

方案1：关联子查询 #

给外层表设置别名，子查询中关联外层门店编号，确保每个门店计算自身的最新日期：

DECLARE @ToDate DATETIME = '2022-11-30 00:00:00.000'  
SELECT 
    s.StoreNumber, 
    s.AdRate,
    s.AdRateDate -- 可根据需求添加其他列
FROM StoreAdRate s
WHERE s.AdRateDate = (
    SELECT MAX(AdRateDate) 
    FROM StoreAdRate 
    WHERE AdRateDate <= @ToDate 
      AND StoreNumber = s.StoreNumber -- 关联外层门店，确保按门店计算最大日期
)

方案2：窗口函数（推荐，多列场景更灵活） #

使用ROW_NUMBER()按门店分组、日期倒序排名，筛选排名为1的记录（即最新费率）：

DECLARE @ToDate DATETIME = '2022-11-30 00:00:00.000'  
WITH RankedRates AS (
    SELECT 
        StoreNumber, 
        AdRate,
        AdRateDate,
        -- 按门店分组，日期从新到旧排序，最新记录排名为1
        ROW_NUMBER() OVER (PARTITION BY StoreNumber ORDER BY AdRateDate DESC) AS RateRank
    FROM StoreAdRate
    WHERE AdRateDate <= @ToDate
)
SELECT 
    StoreNumber, 
    AdRate,
    AdRateDate -- 可根据需求添加其他列
FROM RankedRates
WHERE RateRank = 1

方案对比 #

• 关联子查询逻辑简洁，适合仅需筛选单条件的场景
• 窗口函数方法扩展性更强，若存在同一门店同一日期多条费率记录，可替换ROW_NUMBER()为RANK()或DENSE_RANK()处理并列情况，同时支持返回任意多列数据

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