MySQL查询：如何获取包含前置计算值的表结果

现有items表（按date升序排列） #

查询需求 #

• 计算calculated_cost字段，公式为quantity * cost
• 按行累加calculated_cost得到accumulated_cost，按item分区，遇到新item时重置累加
• 获取previous_accumulated_cost字段，值为上一行的accumulated_cost，首行填0.00，新item的首行同样填0.00

期望输出 #

尝试的错误SQL #

SELECT *,
   (i.quantity * i.cost) AS calculated_cost,
   SUM(i.quantity * i.cost) OVER (PARTITION BY i.item ORDER BY i.date) AS accumulated_cost,
   IFNULL(LAG(i2.accumulated_cost) OVER (PARTITION BY i.item ORDER BY i.date), 0) AS previous_accumulated_cost
FROM items i
LEFT JOIN (
   SELECT item, SUM(quantity * cost) OVER (PARTITION BY item ORDER BY date) AS accumulated_cost
   FROM items 
) i2 ON i.item = i2.item

问题原因及解决方案 #

你的SQL错误在于使用了不必要的JOIN，导致i2.accumulated_cost会和原表每行匹配所有同item的累加值，而非对应行的累加值。正确的做法是直接基于窗口函数计算的结果获取上一行值，无需额外JOIN。

正确SQL查询（简洁子查询版） #

SELECT
    *,
    IFNULL(LAG(accumulated_cost) OVER (PARTITION BY item ORDER BY date), 0.00) AS previous_accumulated_cost
FROM (
    SELECT
        date,
        item,
        quantity,
        cost,
        quantity * cost AS calculated_cost,
        SUM(quantity * cost) OVER (PARTITION BY item ORDER BY date) AS accumulated_cost
    FROM items
) AS temp
ORDER BY date;

说明 #

1. 子查询先计算出calculated_cost和分区累加的accumulated_cost，逻辑清晰易读
2. 外部查询使用LAG()窗口函数，按item分区、date排序，直接获取上一行的accumulated_cost
3. 用IFNULL()将每个分区的首行previous_accumulated_cost替换为0.00
4. 最终按date排序保证结果顺序和原表一致

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