MariaDB技术需求：按日期获取各产品最新2批次及成本关联数据

需求说明 #

现有业务表包含产品、Lot #、日期、成本字段，记录各产品多批次成本信息，需实现：

• 每个产品返回一行数据
• 展示最新批次的Lot #、日期、成本
• 展示上一批次的Last Lot #、Last Date、Last Cost
• 添加成本对比计算列（如成本差值、涨幅百分比）

当前编写的查询代码未正确处理日期排序，需修正方案。

现有数据 #

期望结果 #

现有代码问题分析 #

当前代码存在以下关键问题：

• 排序逻辑错误：按productNumber降序和unitCost排序，而非按日期倒序获取最新批次，无法正确识别最新记录
• 未关联上一批次数据：仅筛选前2条记录，但未将同一产品的最新和上一批次数据合并为一行
• 字段名不匹配：代码中productNumber、unitCost等字段与业务表的产品、成本字段不一致

解决方案 #

方法1：使用窗口函数（推荐，MariaDB 10.2及以上版本） #

利用ROW_NUMBER()窗口函数按产品分组、日期倒序排序，再通过自连接合并最新与上一批次数据：

WITH ranked_batches AS (
    SELECT
        产品,
        `Lot #`,
        日期,
        成本,
        ROW_NUMBER() OVER (PARTITION BY 产品 ORDER BY STR_TO_DATE(日期, '%m/%d/%y') DESC) AS rn
    FROM 业务表名 -- 替换为你的实际表名
)
SELECT
    r1.产品,
    r1.`Lot #`,
    r1.日期,
    r1.成本,
    r2.`Lot #` AS `上一批次Lot #`,
    r2.日期 AS `上一批次日期`,
    r2.成本 AS `上一批次成本`,
    -- 成本对比计算列
    r1.成本 - r2.成本 AS 成本差值,
    ROUND((r1.成本 - r2.成本)/r2.成本*100, 2) AS 成本涨幅百分比
FROM ranked_batches r1
LEFT JOIN ranked_batches r2 
    ON r1.产品 = r2.产品 AND r1.rn = 1 AND r2.rn = 2
WHERE r1.rn = 1
ORDER BY STR_TO_DATE(r1.日期, '%m/%d/%y') DESC;

方法2：使用用户变量（兼容低版本MariaDB） #

若你的MariaDB版本低于10.2，不支持窗口函数，可通过用户变量实现分组排序，再自连接合并数据：

SELECT
    t1.产品,
    t1.`Lot #`,
    t1.日期,
    t1.成本,
    t2.`Lot #` AS `上一批次Lot #`,
    t2.日期 AS `上一批次日期`,
    t2.成本 AS `上一批次成本`,
    t1.成本 - t2.成本 AS 成本差值,
    ROUND((t1.成本 - t2.成本)/t2.成本*100, 2) AS 成本涨幅百分比
FROM (
    SELECT
        *,
        @rn := IF(@prev_product = 产品, @rn + 1, 1) AS rn,
        @prev_product := 产品
    FROM (
        SELECT *
        FROM 业务表名 -- 替换为你的实际表名
        ORDER BY 产品, STR_TO_DATE(日期, '%m/%d/%y') DESC
    ) sorted
    CROSS JOIN (SELECT @prev_product := NULL, @rn := 0) vars
) t1
LEFT JOIN (
    SELECT
        *,
        @rn2 := IF(@prev_product2 = 产品, @rn2 + 1, 1) AS rn,
        @prev_product2 := 产品
    FROM (
        SELECT *
        FROM 业务表名 -- 替换为你的实际表名
        ORDER BY 产品, STR_TO_DATE(日期, '%m/%d/%y') DESC
    ) sorted2
    CROSS JOIN (SELECT @prev_product2 := NULL, @rn2 := 0) vars2
) t2 ON t1.产品 = t2.产品 AND t1.rn = 1 AND t2.rn = 2
WHERE t1.rn = 1
ORDER BY STR_TO_DATE(t1.日期, '%m/%d/%y') DESC;

关键注意事项 #

• 日期格式转换：使用STR_TO_DATE(日期, '%m/%d/%y')将字符串日期转为日期类型，确保排序逻辑正确
• 表名替换：将代码中的业务表名替换为实际数据表名称
• 字段名对齐：若实际字段名与示例不一致，需同步修改代码中的字段名

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