如何用单条T-SQL查询获取指定日期后所有记录及最多N条前置记录

我来帮你搞定这个适配API场景的T-SQL查询需求！先把你给出的样本数据整理成更清晰的表格：

样本数据表 #

核心需求回顾 #

你需要用单条T-SQL语句实现以下逻辑，完美适配API返回「当前/未来记录+指定数量最近历史记录」的场景：

• 查询指定日期及之后的所有记录，同时最多获取该日期之前的指定数量记录
• 场景1：比如查询2018-05-05及之后的所有记录，同时最多带2条该日期之前的记录
• 场景2：如果指定日期之前的记录不够指定数量，就把所有存在的前置记录都带上，再加上后续全部记录
• 场景3：如果指定日期之后没有匹配记录，就只返回最多指定数量的最近前置记录

解决方案代码 #

下面的语句用CTE和窗口函数搞定所有场景，不需要存储过程或函数，直接就能用：

-- 先定义两个参数：目标日期和最多要拿的历史记录数
DECLARE @TargetDate DATE = '2018-05-05';
DECLARE @MaxHistoricalRecords INT = 2;

WITH EventCTE AS (
    SELECT 
        eventDate,
        eventText,
        -- 标记这条记录是在目标日期及之后，还是之前
        CASE WHEN eventDate >= @TargetDate THEN 1 ELSE 0 END AS IsTargetOrLater,
        -- 给目标日期之前的记录按日期倒序排名，最近的排第1
        ROW_NUMBER() OVER (ORDER BY eventDate DESC) AS HistoricalRank
    FROM YourEventTable -- 记得把这里换成你实际的表名哦
)
SELECT eventDate, eventText
FROM EventCTE
WHERE 
    -- 先把所有目标日期及之后的记录都留下
    IsTargetOrLater = 1
    -- 再加上目标日期之前、排名在指定数量内的最近几条历史记录
    OR (IsTargetOrLater = 0 AND HistoricalRank <= @MaxHistoricalRecords)
ORDER BY eventDate ASC;

代码逻辑拆解 #

1. CTE部分：给每条记录打两个标签：
   • IsTargetOrLater：用1/0区分记录是在目标日期及之后，还是之前
   • HistoricalRank：对目标日期之前的记录按日期从新到旧编号，最近的记录编号最小（比如2018-05-04是第1，2018-05-03是第2）
2. 过滤逻辑：
   • 直接保留所有IsTargetOrLater=1的记录，也就是目标日期及之后的全部数据
   • 同时保留IsTargetOrLater=0且排名不超过指定数量的记录，确保最多拿指定条数的最近历史
3. 排序：最后按日期升序返回，符合API展示的逻辑

场景验证 #

• 场景1测试：当@TargetDate='2018-05-05'、@MaxHistoricalRecords=2时，会返回2018-05-04、2018-05-03这两条历史记录，加上2018-05-05及之后的所有记录，完全符合要求
• 场景2测试：如果目标日期设为2018-05-02，最多拿2条历史，但之前只有2018-05-01一条，那么会自动带上这条历史，再加上2018-05-02及之后的所有记录
• 场景3测试：如果目标日期是2018-05-11，之后没有记录，就只会返回最近的2条历史（2018-05-10、2018-05-09）

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