MySQL如何查询各商品最新生效的对应数量阶梯价格

问题分析 #

你原来的SQL存在三个核心问题，导致结果不符合预期：

1. 没有提前过滤valid_from大于当前日期的未来生效记录
2. 使用row_number()会给同一商品、同一生效日期下的多条数量梯度记录分配不同排名，无法批量取出同一生效日期的所有条目
3. 没有在外层筛选排名为1的目标记录

正确实现方案 #

方案1：MySQL 8.0+ 窗口函数版本

SELECT quantity, price, ProdID
FROM (
    SELECT 
        `p`.`qty` AS `quantity`,
        `p`.`price` AS `price`,
        `p`.`ProdID` AS `ProdID`,
        -- 同一商品、同一生效日期的所有记录排名一致
        RANK() OVER (
            PARTITION BY `p`.`ProdID` 
            ORDER BY `p`.`valid_from` DESC
        ) AS rk
    FROM `tblprices` `p`
    -- 先过滤未到生效时间的记录
    WHERE `p`.`valid_from` <= CURDATE()
) t
WHERE rk = 1
ORDER BY ProdID, quantity;

方案2：MySQL 5.x 兼容版本（无窗口函数）

SELECT 
    p.qty AS quantity,
    p.price AS price,
    p.ProdID
FROM tblprices p
INNER JOIN (
    -- 先查询每个商品符合条件的最大生效日期
    SELECT ProdID, MAX(valid_from) AS max_valid
    FROM tblprices
    WHERE valid_from <= CURDATE()
    GROUP BY ProdID
) t ON p.ProdID = t.ProdID AND p.valid_from = t.max_valid
ORDER BY p.ProdID, p.qty;

逻辑说明 #

两个方案均遵循需求规则：

• 完全未使用ID、timestamp字段做筛选条件
• 仅基于valid_from判断生效时间，自动排除未来生效的价格
• 会返回指定生效日期下该商品所有数量梯度的价格记录

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