今天碰到mysql的一个问题, 下一行的一个字段需要累加上一行的字段的值

1
2
3
4
5
6
7
8
9
+----+------+-----+           +----+------+--------------+
| id | name | age |           | id | name | age          |
+----+------+-----+           +----+------+--------------+
| 1  | lily | 10  |           | 1  | lily | 10           |
+----+------+-----+    =>     +----+------+--------------+
| 2  | lucy | 28  |           | 2  | lucy | 28 +10       |
+----+------+-----+           +----+------+--------------+
| 3  | tom  | 80  |           | 3  | tom  | 80 + 28 + 10 |
+----+------+-----+           +----+------+--------------+

如何实现它

  • 可以使用java, php, python等脚本语言轻松实现, 不讨论.

  • 今天讨论用sql语言实现它. 要实现这个东东, 第一感觉想到的是过程

    1. 编写过程

       
      1
      2
      3
      4
      5
      6
      7
      8
      9
      10
      11
      12
      13
      14
      15
      16
      17
      18
      19
      20
      21
      22
      23
      24
      25
      26
      27
      28
      29
      30
      31
      32
      33
      34
      35
      36
      37
      38
      39
      40

      delimiter ;;

      create procedure demo()

      begin

      declare done int default 0;
      declare prevalue int default 0;
      declare vid int;
      declare currentvalue int;

      -- 声明游标

      declare demo_cursor cursor for select id, age from demo;
      declare continue handler for not found set done = 1;

      -- 打开游标

      open demo_cursor;

      -- 循环操作
      read_loop: LOOP

          fetch invest into vid, currentvalue;
          set prevalue := prevalue+currentvalue;

          if done then
              leave read_loop;
          end if;

          update demo set age = prevalue where id = id;

      end LOOP;
      CLOSE demo_cursor;

      end;
      ;;

      delimiter ;

      =======

    2. 编写过程

      1
      2
      3
      4
      5
      6
      7
      8
      9
      10
      11
      12
      13
      14
      15
      16
      17
      18
      19
      20
      21
      22
      23
      24
      25
      26
      27
      28
      29
      30
      31
      32
      33
      34
      35
      36
      37
      38
      39
      40

      delimiter ;;

      create procedure demo()

      begin

      declare done int default 0;
      declare prevalue int default 0;
      declare vid int;
      declare currentvalue int;

      -- 声明游标

      declare demo_cursor cursor for select id, age from demo;
      declare continue handler for not found set done = 1;

      -- 打开游标

      open demo_cursor;

      -- 循环操作
      read_loop: LOOP

          fetch invest into vid, currentvalue;
          set prevalue := prevalue+currentvalue;

          if done then
              leave read_loop;
          end if;

          update demo set age = prevalue where id = id;

      end LOOP;
      CLOSE demo_cursor;

      end;
      ;;

      delimiter ;

    3. 执行过程 call demo();

    4. 删除过程 drop procedure demo;

就这样实现了, 欢迎拍砖.