SQL基础之实现累加值

按员工的编号升序生成一列累计值, 结果按累计值升序排序

结果集举例:

+--------+---------+----------+

| ename  | sal     | sum      |

+--------+---------+----------+

| SMITH  |  800.00 |   800.00 |

| ALLEN  | 1600.00 |  2400.00 |

| WARD   | 1250.00 |  3650.00 |

| JONES  | 2975.00 |  6625.00 |

| MARTIN | 1250.00 |  7875.00 |

| BLAKE  | 2850.00 | 10725.00 |

| CLARK  | 2450.00 | 13175.00 |

| SCOTT  | 3000.00 | 16175.00 |

| KING   | 5000.00 | 21175.00 |

| TURNER | 1500.00 | 22675.00 |

| ADAMS  | 1100.00 | 23775.00 |

| JAMES  |  950.00 | 24725.00 |

| FORD   | 3000.00 | 27725.00 |

| MILLER | 1300.00 | 29025.00 |

+--------+---------+----------+

14 rows in set (0.00 sec)

1、使用窗口函数

[root@liu03 ~]# /usr/local/mysql-8.0.15/bin/mysql -uroot -plky123 --socket=/usr/local/mysql-8.0.15/data/mysql.sock

mysql: [Warning] Using a password on the command line interface can be insecure.

Welcome to the MySQL monitor.  Commands end with ; or g.

Your MySQL connection id is 9

Server version: 8.0.15 MySQL Community Server - GPL

 

Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.

 

Oracle is a registered trademark of Oracle Corporation and/or its

affiliates. Other names may be trademarks of their respective

owners.

 

Type 'help;' or 'h' for help. Type 'c' to clear the current input statement.

 

mysql> use sql2;

Reading table information for completion of table and column names

You can turn off this feature to get a quicker startup with -A

 

Database changed

mysql> select ename,sal from emp;

+--------+---------+

| ename  | sal     |

+--------+---------+

| SMITH  |  800.00 |

| ALLEN  | 1600.00 |

| WARD   | 1250.00 |

| JONES  | 2975.00 |

| MARTIN | 1250.00 |

| BLAKE  | 2850.00 |

| CLARK  | 2450.00 |

| SCOTT  | 3000.00 |

| KING   | 5000.00 |

| TURNER | 1500.00 |

| ADAMS  | 1100.00 |

| JAMES  |  950.00 |

| FORD   | 3000.00 |

| MILLER | 1300.00 |

+--------+---------+

14 rows in set (0.00 sec)

 

mysql> select empno,ename,sal,sum(sal) over(order by empno rows between unbounded preceding and current row) as sum from emp;

+-------+--------+---------+----------+

| empno | ename  | sal     | sum      |

+-------+--------+---------+----------+

|  7369 | SMITH  |  800.00 |   800.00 |

|  7499 | ALLEN  | 1600.00 |  2400.00 |

|  7521 | WARD   | 1250.00 |  3650.00 |

|  7566 | JONES  | 2975.00 |  6625.00 |

|  7654 | MARTIN | 1250.00 |  7875.00 |

|  7698 | BLAKE  | 2850.00 | 10725.00 |

|  7782 | CLARK  | 2450.00 | 13175.00 |

|  7788 | SCOTT  | 3000.00 | 16175.00 |

|  7839 | KING   | 5000.00 | 21175.00 |

|  7844 | TURNER | 1500.00 | 22675.00 |

|  7876 | ADAMS  | 1100.00 | 23775.00 |

|  7900 | JAMES  |  950.00 | 24725.00 |

|  7902 | FORD   | 3000.00 | 27725.00 |

|  7934 | MILLER | 1300.00 | 29025.00 |

+-------+--------+---------+----------+

14 rows in set (0.00 sec)

 

https://dev.mysql.com/doc/refman/8.0/en/window-functions-frames.html

https://dev.mysql.com/doc/refman/8.0/en/window-functions-usage.html

2、使用变量实现累加

mysql> set @sum :=0;

Query OK, 0 rows affected (0.00 sec)

mysql> select ename,sal,(@sum := @sum + sal) as sum from emp;

+--------+---------+----------+

| ename  | sal     | sum      |

+--------+---------+----------+

| SMITH  |  800.00 |   800.00 |

| ALLEN  | 1600.00 |  2400.00 |

| WARD   | 1250.00 |  3650.00 |

| JONES  | 2975.00 |  6625.00 |

| MARTIN | 1250.00 |  7875.00 |

| BLAKE  | 2850.00 | 10725.00 |

| CLARK  | 2450.00 | 13175.00 |

| SCOTT  | 3000.00 | 16175.00 |

| KING   | 5000.00 | 21175.00 |

| TURNER | 1500.00 | 22675.00 |

| ADAMS  | 1100.00 | 23775.00 |

| JAMES  |  950.00 | 24725.00 |

| FORD   | 3000.00 | 27725.00 |

| MILLER | 1300.00 | 29025.00 |

+--------+---------+----------+

14 rows in set (0.00 sec)

3、使用标量子查询

mysql> select b.ename,b.sal,(select sum(a.sal) from emp a where a.empno <= b.empno) as sum from emp b;

+--------+---------+----------+

| ename  | sal     | sum      |

+--------+---------+----------+

| SMITH  |  800.00 |   800.00 |

| ALLEN  | 1600.00 |  2400.00 |

| WARD   | 1250.00 |  3650.00 |

| JONES  | 2975.00 |  6625.00 |

| MARTIN | 1250.00 |  7875.00 |

| BLAKE  | 2850.00 | 10725.00 |

| CLARK  | 2450.00 | 13175.00 |

| SCOTT  | 3000.00 | 16175.00 |

| KING   | 5000.00 | 21175.00 |

| TURNER | 1500.00 | 22675.00 |

| ADAMS  | 1100.00 | 23775.00 |

| JAMES  |  950.00 | 24725.00 |

| FORD   | 3000.00 | 27725.00 |

| MILLER | 1300.00 | 29025.00 |

+--------+---------+----------+

14 rows in set (0.00 sec)