mysql根据前一个日期计算

Question

我有一个奇怪的问题，计算是基于今天的值减去前一天的值
这是查询。样本数据及查询结果 here

SELECT pd.id,pd.price_date,pd.name_id,pd.class_id,pd.currency_id,pd.price,
         pd.price - (SELECT price
                    FROM price_data as x
                    WHERE x.price_date < pd.price_date
                    AND x.name_id      = pd.name_id
                    AND x.class_id     = pd.class_id
                    AND x.currency_id  = pd.currency_id
      HAVING MAX(x.price_date)) as `change`
      FROM price_data as pd
      WHERE pd.name_id ='BILL'

      ORDER bY pd.name_id,pd.class_id,pd.currency_id,pd.price_date    

如果您查看结果集，您会发现计算一直有效，直到它在 第 6 行 处首次失败。好像开始把之前的改动加到现在的改动上。

日期判断有问题吗？

Answer 1

您想要的查询是:

SELECT pd.id,pd.price_date,pd.name_id,pd.class_id,pd.currency_id,pd.price,
         pd.price - (SELECT price
                     FROM price_data as x
                     WHERE x.price_date < pd.price_date
                     AND x.name_id      = pd.name_id
                     AND x.class_id     = pd.class_id
                     AND x.currency_id  = pd.currency_id
                     order by x.price_date desc
                     limit 1
                    ) as `change`
FROM price_data as pd
WHERE pd.name_id ='BILL'
ORDER bY pd.name_id,pd.class_id,pd.currency_id,pd.price_date;

您的原始查询有什么问题？嗯，having 子句是一个非操作，因为它只是检查 price_date 的最大值不为 0。要得到你想要的，你需要对数据进行排序并取一个值你感兴趣。