如何查询指定日期范围内的行，同时获取同FK ID早于范围的最新行？

针对你的需求，我们可以通过预先筛选符合条件的外键ID再合并数据的方式实现，以下提供两种兼容不同数据库的写法： #

前置说明 #

假设你的表名为your_table，传入的日期范围起始为@StartDate、结束为@EndDate，示例中对应值为'2021-11-01'和'2021-11-30'，可根据实际使用的数据库调整日期格式和变量声明方式。 #

方案1：通用CTE写法（兼容所有支持CTE的数据库） #

该写法逻辑清晰，无需依赖窗口函数，兼容性更好：

-- 1. 先提取所有在目标日期范围内出现过的外键ID
WITH valid_fk AS (
    SELECT DISTINCT `FK ID`
    FROM your_table
    WHERE `Date` BETWEEN @StartDate AND @EndDate
),
-- 2. 提取日期范围内的所有目标行
range_rows AS (
    SELECT ID, `FK ID`, `Date`
    FROM your_table
    WHERE `Date` BETWEEN @StartDate AND @EndDate
),
-- 3. 提取符合条件的外键对应的早于范围的最新一行
pre_range_latest_rows AS (
    SELECT t.ID, t.`FK ID`, t.`Date`
    FROM your_table t
    INNER JOIN valid_fk v ON t.`FK ID` = v.`FK ID`
    WHERE t.`Date` < @StartDate
    AND (t.`FK ID`, t.`Date`) IN (
        SELECT `FK ID`, MAX(`Date`)
        FROM your_table t2
        INNER JOIN valid_fk v2 ON t2.`FK ID` = v2.`FK ID`
        WHERE t2.`Date` < @StartDate
        GROUP BY `FK ID`
    )
)
-- 合并两部分结果，按外键和日期排序
SELECT * FROM range_rows
UNION ALL
SELECT * FROM pre_range_latest_rows
ORDER BY `FK ID`, `Date`;

方案2：窗口函数写法（适用MySQL 8.0+/PostgreSQL/SQL Server等新版数据库） #

如果你的数据库支持窗口函数，可以用更简洁的写法实现：

WITH filter_rows AS (
    SELECT 
        ID,
        `FK ID`,
        `Date`,
        -- 对每个外键下早于起始日期的行按日期倒序排序，排名第一的即为最新行
        ROW_NUMBER() OVER(PARTITION BY `FK ID` ORDER BY `Date` DESC) AS rn
    FROM your_table
    WHERE
        -- 保留范围内的所有行
        `Date` BETWEEN @StartDate AND @EndDate
        OR (
            -- 保留符合条件的外键的历史行
            `Date` < @StartDate
            AND `FK ID` IN (SELECT DISTINCT `FK ID` FROM your_table WHERE `Date` BETWEEN @StartDate AND @EndDate)
        )
)
SELECT ID, `FK ID`, `Date`
FROM filter_rows
-- 范围内的行全部保留，历史行只保留最新的一条
WHERE `Date` BETWEEN @StartDate AND @EndDate OR rn = 1
ORDER BY `FK ID`, `Date`;

原方案问题说明 #

你之前的UNION方案返回无效行，是因为第二部分查询没有限定仅处理范围内出现过的外键ID，上述两种方案都通过预先提取valid_fk列表过滤了无关外键的历史行，不会再返回ID=5这类无效数据。

内容的提问来源于stack exchange，提问作者ryan.kom