现有表 salary，如图1：
实现需求：按员工的编号升序生成一列累计值, 结果按累计值升序排序，实现结果如图2。
图1.表 salary 图2.需要实现的结果
建表语句如下：
CREATE TABLE `salary` ( `empno` int NOT NULL, `ename` varchar(255) DEFAULT NULL, `salary` decimal(10,2) DEFAULT NULL, PRIMARY KEY (`empno`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
插入数据：
INSERT INTO salary(empno,ename,salary) VALUES(7934,‘MILLER‘,1300.00); INSERT INTO salary(empno,ename,salary) VALUES(7902,‘FORD‘,3000.00); INSERT INTO salary(empno,ename,salary) VALUES(7900,‘JAMES‘,950.00); INSERT INTO salary(empno,ename,salary) VALUES(7876,‘ADAMS‘,1100.00); INSERT INTO salary(empno,ename,salary) VALUES(7844,‘TURNER‘,1500.00); INSERT INTO salary(empno,ename,salary) VALUES(7839,‘KING‘,5000.00); INSERT INTO salary(empno,ename,salary) VALUES(7788,‘SCOTT‘,3000.00); INSERT INTO salary(empno,ename,salary) VALUES(7782,‘CLARK‘,2450.00); INSERT INTO salary(empno,ename,salary) VALUES(7698,‘BLAKE‘,2850.00); INSERT INTO salary(empno,ename,salary) VALUES(7654,‘MARTIN‘,1250.00); INSERT INTO salary(empno,ename,salary) VALUES(7566,‘JONES‘,2975.00); INSERT INTO salary(empno,ename,salary) VALUES(7521,‘WARD‘,1250.00); INSERT INTO salary(empno,ename,salary) VALUES(7499,‘ALLEN‘,1600.00); INSERT INTO salary(empno,ename,salary) VALUES(7369,‘SMITH‘,800.00);
解法一：使用窗口函数
SELECT empno,ename,salary,sum(salary) over (ORDER BY empno) as sum FROM salary;
解法二：使用变量实现累加
set @sum :=0; SELECT empno,ename,salary,(@sum := @sum + salary) as sum FROM salary ORDER BY empno;
解法三：使用标量子查询
SELECT b.empno,b.ename,b.salary, (SELECT sum(a.salary) FROM salary a WHERE a.empno <= b.empno) as sum FROM salary b;
原文：https://www.cnblogs.com/yangms/p/14091731.html