mysql之累加

最新推荐文章于 2024-09-16 11:29:16 发布

jgmydsai

最新推荐文章于 2024-09-16 11:29:16 发布

阅读量8.2k

点赞数

本文链接：https://blog.csdn.net/jgmydsai/article/details/39998849

版权

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)