MySQL中如何根据validFrom日期筛选今日有效价格的行

这是个很常见的分组取最新有效数据的需求，我给你提供几种简洁高效的MySQL实现方式，你可以根据你的MySQL版本选择合适的方案：

先准备测试数据（方便你验证） #

CREATE TABLE IF NOT EXISTS price_history (
    type INT,
    validfrom DATE,
    price INT
);

INSERT INTO price_history VALUES
(1, '2018-01-15', 10),
(1, '2018-01-20', 20),
(1, '2018-01-25', 30),
(2, '2018-01-01', 12),
(3, '2018-01-01', 18);

方法1：使用窗口函数（MySQL 8.0+ 推荐） #

这是最直观简洁的写法，利用ROW_NUMBER()窗口函数给每个类型的有效记录按日期倒序编号，取编号为1的就是最新记录：

SELECT type, validfrom, price
FROM (
    SELECT 
        type, 
        validfrom, 
        price,
        -- 按type分组，每个组内按validfrom倒序排序，生成序号
        ROW_NUMBER() OVER (PARTITION BY type ORDER BY validfrom DESC) AS rn
    FROM price_history
    -- 筛选出不晚于当前日期的记录
    WHERE validfrom <= '2018-01-21'
) t
-- 取每个组的第一条（最新的）记录
WHERE rn = 1;

方法2：关联子查询（兼容MySQL 5.x） #

如果你的MySQL版本还不支持窗口函数，可以用这种写法，逻辑是：找出每条符合日期条件的记录，且同类型下没有比它更晚的有效记录，那它就是该类型的最新记录：

SELECT ph.type, ph.validfrom, ph.price
FROM price_history ph
WHERE ph.validfrom <= '2018-01-21'
AND NOT EXISTS (
    SELECT 1 
    FROM price_history ph2
    WHERE ph2.type = ph.type
    AND ph2.validfrom <= '2018-01-21'
    -- 存在同类型且日期更晚的有效记录，则当前记录不是最新的
    AND ph2.validfrom > ph.validfrom
);

方法3：MAX()分组关联（兼容所有版本） #

先分组找出每个类型的最大有效日期（不晚于当前日期），再关联原表获取对应的价格：

SELECT ph.type, ph.validfrom, ph.price
FROM price_history ph
JOIN (
    -- 先获取每个type的最新有效日期
    SELECT type, MAX(validfrom) AS max_validfrom
    FROM price_history
    WHERE validfrom <= '2018-01-21'
    GROUP BY type
) ph_max ON ph.type = ph_max.type AND ph.validfrom = ph_max.max_validfrom;

补充说明 #

• 如果你的表数据量较大，建议给type和validfrom创建联合索引：CREATE INDEX idx_type_validfrom ON price_history(type, validfrom);，这能大幅提升查询效率。
• 窗口函数的写法最易读和维护，优先推荐在MySQL8.0及以上版本使用。

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