mysql之累加

来源：互联网发布：淘宝免费视频教材编辑：程序博客网时间：2024/06/06 16:25

SET @add_sal=0;  SELECT deptno, empno, ename, sal, @add_sal := @add_sal + sal AS add_sal  FROM emp ORDER BY empno;+--------+-------+--------+------+---------+| deptno | empno | ename  | sal  | add_sal |+--------+-------+--------+------+---------+|     20 |  7369 | SMITH  |  800 |     800 ||     30 |  7499 | ALLEN  | 1600 |    2400 ||     30 |  7521 | WARD   | 1250 |    3650 ||     20 |  7566 | JONES  | 2975 |    6625 ||     30 |  7654 | MARTIN | 1250 |    7875 ||     30 |  7698 | BLAKE  | 2850 |   10725 ||     10 |  7782 | CLARK  | 2450 |   13175 ||     20 |  7788 | SCOTT  | 3000 |   16175 ||     10 |  7839 | KING   | 5000 |   21175 ||     30 |  7844 | TURNER | 1500 |   22675 ||     20 |  7876 | ADAMS  | 1100 |   23775 ||     30 |  7900 | JAMES  |  950 |   24725 ||     20 |  7902 | FORD   | 3000 |   27725 ||     10 |  7934 | MILLER | 1300 |   29025 |+--------+-------+--------+------+---------+14 rows in set (0.01 sec)

SET @add_sal=0;     SET @last_deptno=-1;    SELECT deptno,         empno,         ename,         sal,         IF(@last_deptno = deptno, @rn := @rn + 1, @rn := 1) AS rn,         IF(@last_deptno = deptno, @add_sal := @add_sal + sal, @add_sal := sal) AS add_sal,         @last_deptno := deptno AS last_deptno    FROM emp   ORDER BY deptno, sal;+--------+-------+--------+------+------+---------+-------------+| deptno | empno | ename  | sal  | rn   | add_sal | last_deptno |+--------+-------+--------+------+------+---------+-------------+|     10 |  7934 | MILLER | 1300 |    1 |    1300 |          10 ||     10 |  7782 | CLARK  | 2450 |    2 |    3750 |          10 ||     10 |  7839 | KING   | 5000 |    3 |    8750 |          10 ||     20 |  7369 | SMITH  |  800 |    1 |     800 |          20 ||     20 |  7876 | ADAMS  | 1100 |    2 |    1900 |          20 ||     20 |  7566 | JONES  | 2975 |    3 |    4875 |          20 ||     20 |  7788 | SCOTT  | 3000 |    4 |    7875 |          20 ||     20 |  7902 | FORD   | 3000 |    5 |   10875 |          20 ||     30 |  7900 | JAMES  |  950 |    1 |     950 |          30 ||     30 |  7654 | MARTIN | 1250 |    2 |    2200 |          30 ||     30 |  7521 | WARD   | 1250 |    3 |    3450 |          30 ||     30 |  7844 | TURNER | 1500 |    4 |    4950 |          30 ||     30 |  7499 | ALLEN  | 1600 |    5 |    6550 |          30 ||     30 |  7698 | BLAKE  | 2850 |    6 |    9400 |          30 |+--------+-------+--------+------+------+---------+-------------+14 rows in set (0.05 sec)

0 0