按ID查询指定日期前最新状态为Active的数据

针对你的需求——给定查询日期，返回截至该日期（含）每个ID的最新状态为"Active"的记录，这里提供两种通用的SQL实现方案，适配不同数据库环境：

方案一：使用窗口函数（推荐，适用于MySQL 8+、PostgreSQL、SQL Server等） #

利用ROW_NUMBER()窗口函数为每个ID的记录按日期倒序编号，取编号为1的最新记录，再筛选状态为Active且日期不晚于查询日期的数据：

WITH ranked_records AS (
    SELECT 
        Date,
        Status,
        ID,
        ROW_NUMBER() OVER (PARTITION BY ID ORDER BY Date DESC) AS rn
    FROM your_table_name
    WHERE Date <= '8/1/22' -- 替换为你的查询日期
)
SELECT Date, Status, ID
FROM ranked_records
WHERE rn = 1 AND Status = 'Active';

逻辑说明： #

• PARTITION BY ID：按ID分组，确保每个ID的记录单独排序
• ORDER BY Date DESC：每组内按日期倒序，最新的记录排在最前面
• rn = 1：筛选出每个ID的最新记录
• 最后加上Status = 'Active'：只保留最新状态为Active的ID记录

方案二：兼容旧版数据库的子查询方案 #

如果你的数据库不支持窗口函数，可以用子查询先获取每个ID在查询日期前的最大日期，再关联原表筛选符合条件的记录：

SELECT t1.Date, t1.Status, t1.ID
FROM your_table_name t1
INNER JOIN (
    SELECT ID, MAX(Date) AS latest_date
    FROM your_table_name
    WHERE Date <= '8/1/22' -- 替换为你的查询日期
    GROUP BY ID
) t2 ON t1.ID = t2.ID AND t1.Date = t2.latest_date
WHERE t1.Status = 'Active';

逻辑说明： #

• 子查询t2：计算每个ID在查询日期及之前的最新日期
• 关联原表t1：获取每个ID对应最新日期的完整记录
• WHERE t1.Status = 'Active'：筛选出最新状态为Active的记录

验证示例 #

用你提供的示例数据测试查询日期8/1/22，两种方案都会返回：

A         B         C
 Date   | Status   | ID 
--------|----------|-------
5/1/22  | Active   | Bob
6/1/22  | Active   | Jim

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