按员工的编号升序生成一列累计值, 结果按累计值升序排序
结果集举例:
+--------+---------+----------+
| 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) |