如何获取指定时间区间数据行及各FKId对应的前一行数据？

嘿，这个需求可以通过CTE（公共表表达式）结合窗口函数轻松搞定，我给你一步步拆解清楚：

需求拆解 #

我们需要拿到两类数据：

• 第一类：日期恰好是2018-01-20和2018-01-21的原始数据行
• 第二类：上述每行数据所属FKId对应的最新前一行（也就是该FKId下，日期早于目标行的最后一条记录）

通用SQL实现（支持多数现代数据库） #

假设你的表名为your_table，可以用以下SQL语句：

WITH target_rows AS (
    -- 第一步：筛选出目标日期的所有行
    SELECT *
    FROM your_table
    WHERE CAST(Date AS DATE) IN ('2018-01-20', '2018-01-21')
),
prev_rows AS (
    -- 第二步：找出每个目标行对应的FKId的最新前一行
    SELECT t.*
    FROM your_table t
    JOIN target_rows tr ON t.FKId = tr.FKId
    WHERE t.Date < tr.Date
    -- 按FKId分组，日期倒序取第一行（最新的前一行）
    QUALIFY ROW_NUMBER() OVER (PARTITION BY tr.Id, t.FKId ORDER BY t.Date DESC) = 1
)
-- 合并两类数据并按FKId、日期排序
SELECT * FROM target_rows
UNION ALL
SELECT * FROM prev_rows
ORDER BY FKId, Date;

适配MySQL的写法（不支持QUALIFY语法） #

如果你的数据库是MySQL（版本低于8.0.13不支持QUALIFY），可以调整成下面的写法：

WITH target_rows AS (
    SELECT *
    FROM your_table
    WHERE DATE(Date) IN ('2018-01-20', '2018-01-21')
),
ranked_prev_rows AS (
    SELECT 
        t.*,
        -- 给每个目标行对应的前序记录按日期倒序排名
        ROW_NUMBER() OVER (PARTITION BY tr.Id, t.FKId ORDER BY t.Date DESC) AS rn
    FROM your_table t
    JOIN target_rows tr ON t.FKId = tr.FKId
    WHERE t.Date < tr.Date
)
SELECT * FROM target_rows
UNION ALL
-- 取排名第一的前序记录
SELECT Id, FKId, Amount1, Amount2, Date FROM ranked_prev_rows WHERE rn = 1
ORDER BY FKId, Date;

结果验证 #

按照你提供的测试数据，最终返回的结果会包含4行：

1. Id=1, FKId=1, 2018-01-18（FKId=1的前一行）
2. Id=3, FKId=1, 2018-01-20（目标行）
3. Id=2, FKId=2, 2018-01-19（FKId=2的前一行）
4. Id=5, FKId=2, 2018-01-21（目标行）

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