mysql - Mysql Query 中不同列的计算并将计算的输出记录在下面的一行中

Question

我有一张 table :-

 ----------------+--------------+--------------+--------------------+
 | IsFirstChunk | BufferedTime       | ElapsedTime | TotalBufferTime|
 +----+--------------+----------------+--------------+--------------+
 | Yes          | 9.056693603959156  | 13.434      | NULL            |
 | NULL         | 8.397168452045355  | 13.915      | NULL            |
 | NULL         | 9.480674058760885  | 14.576      | NULL            |
 | NULL         | 8.25989921902355   | 15.816      | NULL            |
 | NULL         | 9.029792805273495  | 18.573      | NULL            |
 | NULL         | 9.55641146650022   | 26.385      | NULL            |
 | NULL         | 7.567281197192748  | 37.274      | NULL            |
 | NULL         | 9.046965152922192  | 46.142      | NULL            |
 | NULL         | 8.135399698570493  | 56.825      | NULL            |

我想计算 BufferTime 和 ElapsedTime 列并将该输出插入到 TotalBufferTime 列，但我想将输出记录在 TotalBufferTime 列下面的一行而不是相邻行中。

我想做这样的事情:-

update RequestInfo SET `TotalBufferTime` = BufferedTime + ElapsedTime;

唯一的事情是我想记录 TotalBufferTime 下面一行的输出

+--------------+--------------+--------------+--------------------+
| IsFirstChunk | BufferedTime       | ElapsedTime | TotalBufferTime|
+----+--------------+----------------+--------------+--------------+
| Yes          | 9.056693603959156  | 13.434      | 0              |
| NULL         | 8.397168452045355  | 13.915      | 22.4906936     |
| NULL         | 9.480674058760885  | 14.576      | 22.31216845    |   

Answer 1

试试这个:

update RequestInfo join (
    select curr.id, (
        select (prior.BufferedTime + prior.ElapsedTime) AS value  
          from RequestInfo prior
         where prior.id < curr.id
      order by prior.id desc
       limit 1) value
      from RequestInfo curr
  ) g on g.id = RequestInfo.id
set RequestInfo.TotalBufferTime = g.value;