如何从 MySql Query 中获取以前的记录值 [英] How to get previous record value from MySql Query

问题描述

我想得到以前的记录值与当前值一起计算；

I want to get previous record value to calculate with current value;

示例:col3 的当前值将是 =(前一个 col3 值 + 当前 col2 值)

example: current value of col3 will be = (previous col3 value + current col2 value)

注意:维护 col1 顺序并不重要.因为结果记录是通过 Select 查询获取的.

Note: It is not important to maintain col1 order. Because the resulted records are fetched by Select query.

col1 | col2 | col3
------------------
a    | 1    | 1
b    | 2    | 3  <= 1+2
a    | 1    | 4  <= 3+1
d    | 3    | 7  <= 4+3

我觉得没那么难但是我用mysql解决不了问题

I think it is not so hard but I can not solve the problem using mysql

推荐答案

假设一个起始表如下:

mysql> SELECT * FROM test;
+------+------+
| col1 | col2 |
+------+------+
| a    |    1 |
| b    |    2 |
| c    |    1 |
| d    |    3 |
+------+------+

...你可以通过这样做得到你想要的结果:

... you can get the result you want by doing this:

mysql> SELECT col1, col2, @a := @a + col2 AS col3 
    -> FROM test JOIN (SELECT @a := 0) t
    -> ORDER BY col1;
+------+------+------+
| col1 | col2 | col3 |
+------+------+------+
| a    |    1 |    1 |
| b    |    2 |    3 |
| c    |    1 |    4 |
| d    |    3 |    7 |
+------+------+------+
4 rows in set (0.00 sec)

如果颠倒顺序，就按照你的问题陈述得到结果，即col2加上前面的col3 按照显示的顺序:

If you reverse the order, you get the results according to your problem statement, i.e. col2 plus the previous col3 in the order displayed:

mysql> SELECT col1, col2, @a := @a + col2 AS col3 
    -> FROM test JOIN (SELECT @a := 0) t
    -> ORDER BY col1 DESC;
+------+------+------+
| col1 | col2 | col3 |
+------+------+------+
| d    |    3 |    3 |
| c    |    1 |    4 |
| b    |    2 |    6 |
| a    |    1 |    7 |
+------+------+------+
4 rows in set (0.00 sec)

这篇关于如何从 MySql Query 中获取以前的记录值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！