T-SQL查询截至指定日期每人最新FavoriteMovie值

表结构与规则说明 #

目标表包含字段：

• RecordID：表主键（PK）
• Name：人员姓名
• Date：信息采集日期
• FavoriteMovie：对应采集日登记的人员最喜欢电影
  数据写入规则：每日全量采集所有人员的最爱电影信息，仅当本次采集值和该人员已存的最新记录值不一致时，才追加写入新行。
  查询需求：返回截至2022年5月15日，每位人员对应的最新有效最爱电影值。

实现代码 #

推荐写法（窗口函数，SQL Server 2012+ 支持，性能稳定） #

通过窗口函数按人员分组，对截止日期内的记录按采集时间倒序排序，取每组第一条即为最新值，额外加主键倒序排序避免同日期多记录的异常场景：

WITH TempRanked AS (
    SELECT
        Name,
        FavoriteMovie,
        ROW_NUMBER() OVER (
            PARTITION BY Name 
            ORDER BY Date DESC, RecordID DESC
        ) AS rn
    FROM 替换为你的实际表名
    WHERE Date <= '2022-05-15'
)
SELECT Name, FavoriteMovie AS CurrentFavoriteMovie
FROM TempRanked
WHERE rn = 1;

兼容写法（适配所有SQL Server版本） #

如果是SQL Server 2008及更早不支持窗口函数的版本，可以用分组聚合关联的写法：

SELECT t.Name, t.FavoriteMovie AS CurrentFavoriteMovie
FROM 替换为你的实际表名 t
JOIN (
    SELECT Name, MAX(Date) AS MaxValidDate
    FROM 替换为你的实际表名
    WHERE Date <= '2022-05-15'
    GROUP BY Name
) tmp ON t.Name = tmp.Name AND t.Date = tmp.MaxValidDate

注意事项 #

• 两种写法都先过滤了2022年5月15日之后的采集记录，保证结果符合截止时间要求
• 推荐写法中增加了RecordID DESC排序，避免同一人员同一日期存在多条记录时返回重复结果，更贴合表主键递增的写入逻辑
• 兼容写法如果遇到同人员同日期多条记录的场景，会返回多条结果，可根据业务需要调整去重逻辑

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