如何对具有相同ID的新条目和最后插入条目进行求和并在新条目中插入结果 [英] How to sum new and last inserted entry with same ID and insert result in new entry

问题描述

我正在尝试使用PHP和MYSQL自动计算平均燃料消耗量.但是我不知道该怎么做.这是解释:

I'm trying to make automatic sum of average consumption of fuel using PHP and MYSQL. But I do not know how to do it. This is explanation:

Tabe消费:

ID   CARID   LI        KM          DATETIME         AVERAGE
--------------------------------------------------------------
6     9     70.17   174857   2015-02-14 12:58:51      9.44
5     5     51.00   154785   2015-02-13 10:11:19      8.73
4     8     99.44   485627   2015-02-12 11:45:48      6.84
3     9     47.78   174114   2015-02-11 10:21:32  /first entry
2     8     24.74   484175   2015-02-10 10:28:37  /first entry
1     5     89.65   154201   2015-02-09 10:01:14  /first entry

*数据为例，我希望如何显示.除了AVERAGE的所有内容，其他所有内容都有效，这就是为什么我在这里.

*Data as an example how I want to look like. Everything works except AVERAGE colum, that's why I'm here.

我正在尝试使php函数为具有相同CAREID的新的和最后一个KM条目的每个新条目求和(例如CARID 9):

I'm trying to make php function that will make sum for every new entry of new and last KM entry with same CAREID something like this (exemple for CARID 9) :

• 新条目KM 174857 - 最新条目KM 174114 = 743
• 新条目LI 70.17(用于CARID 9)，总计70.17/(743/100)
• 将结果插入为新条目AVERAGE .

• New entry KM 174857 - Last entry KM 174114 = 743
• New entry LI 70.17 (for CARID 9), in this sum 70.17 / (743 / 100)
• insert result as New entry AVERAGE .

我花了很多时间试图使它正常工作，但我什至从未接近过.

I spent a lot of time trying to make this working but simply I never been even close.

推荐答案

在我看来，执行此操作的适当方法是使用BEFORE INSERT触发器.这样的触发器可能看起来像这样:

It seems to me that the appropriate way to do this is with a BEFORE INSERT trigger. Such a trigger may look like this:

delimiter //
create trigger avg_calc before insert on consum 
  for each row 
  begin
    declare lastOdo int;       -- variable to hold the last odometer reading
    select km
      into lastOdo             -- store the last reading here
      from consum
      where carid = NEW.carid  -- for the carid we are inserting
      order by `datetime` desc -- get the last one by date
      limit 1;
    set NEW.average = (NEW.km - lastOdo) / NEW.li;  -- update the average we're about to insert
  end//
delimiter ;

然后，每当为该车插入新条目时，这将自动平均每辆车的最后两个条目.

This will then automatically average the last two entries per car every time a new entry is inserted for that car.

在此处演示

这篇关于如何对具有相同ID的新条目和最后插入条目进行求和并在新条目中插入结果的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！