SQL Server实现类似Excel VLOOKUP精确匹配的1年前收盘价查询

核心思路 #

不用递归CTE，直接通过CROSS APPLY结合TOP 1实现逐行匹配——对每条记录的mk_dt，筛选出所有早于等于mk_dt减365天的交易日记录，再按日期倒序取第一条，就是符合要求的最近前一个交易日收盘价。

代码示例 #

假设Market_Rates表结构包含：mk_dt（日期类型，如DATE）、close_price（收盘价数值型）。如果是多品种场景（比如有instrument_id字段区分不同标的），只需添加对应匹配条件即可。

单品种场景

SELECT 
    mr.mk_dt,
    mr.close_price AS 当前收盘价,
    prev.close_price AS 前一年对应交易日收盘价
FROM Market_Rates mr
CROSS APPLY (
    -- 筛选目标日期前的所有交易日，取最近的一条
    SELECT TOP 1 close_price
    FROM Market_Rates
    WHERE mk_dt <= DATEADD(DAY, -365, mr.mk_dt)
    ORDER BY mk_dt DESC
) prev

多品种场景（区分标的）

SELECT 
    mr.instrument_id,
    mr.mk_dt,
    mr.close_price AS 当前收盘价,
    prev.close_price AS 前一年对应交易日收盘价
FROM Market_Rates mr
CROSS APPLY (
    SELECT TOP 1 close_price
    FROM Market_Rates
    WHERE instrument_id = mr.instrument_id  -- 匹配同一标的
      AND mk_dt <= DATEADD(DAY, -365, mr.mk_dt)
    ORDER BY mk_dt DESC
) prev

若mk_dt为字符串格式（如CHAR(8)）

先转换为日期类型再处理：

SELECT 
    mr.mk_dt,
    mr.close_price AS 当前收盘价,
    prev.close_price AS 前一年对应交易日收盘价
FROM Market_Rates mr
CROSS APPLY (
    SELECT TOP 1 close_price
    FROM Market_Rates
    WHERE CONVERT(DATE, mk_dt, 112) <= DATEADD(DAY, -365, CONVERT(DATE, mr.mk_dt, 112))
    ORDER BY CONVERT(DATE, mk_dt, 112) DESC
) prev

性能优化建议 #

为mk_dt字段（或多品种场景下的instrument_id + mk_dt复合字段）创建非聚集索引，能大幅提升子查询的匹配效率：

-- 单品种索引
CREATE NONCLUSTERED INDEX IX_Market_Rates_mk_dt ON Market_Rates(mk_dt) INCLUDE(close_price);

-- 多品种索引
CREATE NONCLUSTERED INDEX IX_Market_Rates_instrument_mk_dt ON Market_Rates(instrument_id, mk_dt) INCLUDE(close_price);

为什么不推荐递归CTE？ #

递归CTE更适合处理层级数据（如组织架构）或连续日期生成场景，而本需求只需对每行记录匹配最近的前驱交易日，APPLY + TOP 1的逻辑更直接，性能也更优，无需额外的递归开销。

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