我要招人

去企业版

首页 > 试题广场 >

统计salary的累计和running_total

[编程题]统计salary的累计和running_total

热度指数：124929 时间限制：C/C++ 1秒，其他语言2秒空间限制：C/C++ 32M，其他语言64M
算法知识视频讲解

按照salary的累计和running_total，其中running_total为前两个员工的salary累计和，其他以此类推。具体结果如下Demo展示。。

注意：running_total要的是对在职员工的salary进行cumulative sum，在职员工的筛选条件为to_date = "9999-01-01"

CREATE TABLE `salaries` ( `emp_no` int(11) NOT NULL,
`salary` int(11) NOT NULL,
`from_date` date NOT NULL,
`to_date` date NOT NULL,
PRIMARY KEY (`emp_no`,`from_date`));
输出格式:

emp_no	salary	running_total
10001	88958	88958
10002	72527	161485
10003	43311	204796
10004	74057	278853
10005	94692	373545
10006	43311	416856
10007	88070	504926
10009	95409	600335
10010	94409	694744
10011	25828	720572

示例1

输入

drop table if exists  `salaries` ; 
CREATE TABLE `salaries` (
`emp_no` int(11) NOT NULL,
`salary` int(11) NOT NULL,
`from_date` date NOT NULL,
`to_date` date NOT NULL,
PRIMARY KEY (`emp_no`,`from_date`));
INSERT INTO salaries VALUES(10001,60117,'1986-06-26','1987-06-26');
INSERT INTO salaries VALUES(10001,62102,'1987-06-26','1988-06-25');
INSERT INTO salaries VALUES(10001,66074,'1988-06-25','1989-06-25');
INSERT INTO salaries VALUES(10001,66596,'1989-06-25','1990-06-25');
INSERT INTO salaries VALUES(10001,66961,'1990-06-25','1991-06-25');
INSERT INTO salaries VALUES(10001,71046,'1991-06-25','1992-06-24');
INSERT INTO salaries VALUES(10001,74333,'1992-06-24','1993-06-24');
INSERT INTO salaries VALUES(10001,75286,'1993-06-24','1994-06-24');
INSERT INTO salaries VALUES(10001,75994,'1994-06-24','1995-06-24');
INSERT INTO salaries VALUES(10001,76884,'1995-06-24','1996-06-23');
INSERT INTO salaries VALUES(10001,80013,'1996-06-23','1997-06-23');
INSERT INTO salaries VALUES(10001,81025,'1997-06-23','1998-06-23');
INSERT INTO salaries VALUES(10001,81097,'1998-06-23','1999-06-23');
INSERT INTO salaries VALUES(10001,84917,'1999-06-23','2000-06-22');
INSERT INTO salaries VALUES(10001,85112,'2000-06-22','2001-06-22');
INSERT INTO salaries VALUES(10001,85097,'2001-06-22','2002-06-22');
INSERT INTO salaries VALUES(10001,88958,'2002-06-22','9999-01-01');
INSERT INTO salaries VALUES(10002,72527,'1996-08-03','1997-08-03');
INSERT INTO salaries VALUES(10002,72527,'1997-08-03','1998-08-03');
INSERT INTO salaries VALUES(10002,72527,'1998-08-03','1999-08-03');
INSERT INTO salaries VALUES(10002,72527,'1999-08-03','2000-08-02');
INSERT INTO salaries VALUES(10002,72527,'2000-08-02','2001-08-02');
INSERT INTO salaries VALUES(10002,72527,'2001-08-02','9999-01-01');
INSERT INTO salaries VALUES(10003,40006,'1995-12-03','1996-12-02');
INSERT INTO salaries VALUES(10003,43616,'1996-12-02','1997-12-02');
INSERT INTO salaries VALUES(10003,43466,'1997-12-02','1998-12-02');
INSERT INTO salaries VALUES(10003,43636,'1998-12-02','1999-12-02');
INSERT INTO salaries VALUES(10003,43478,'1999-12-02','2000-12-01');
INSERT INTO salaries VALUES(10003,43699,'2000-12-01','2001-12-01');
INSERT INTO salaries VALUES(10003,43311,'2001-12-01','9999-01-01');

输出

emp_no|salary|running_total
10001|88958|88958
10002|72527|161485
10003|43311|204796

算法知识视频讲解

想吃卤蛋的候选人真的会谢

发表于 2020-10-08 17:12:23

SELECT emp_no, salary, SUM(salary) OVER(ORDER BY emp_no) AS running_total FROM salaries WHERE to_date = '9999-01-01'窗口函数直接搞定

数据分析阿宇君

发表于 2020-08-14 09:17:35

答案： SELECT s1.emp_no, s1.salary, (SELECT SUM(s2.salary) FROM salaries AS s2 WHERE s2.emp_no <= s1.emp_no AND s2.to_date = '9999-01-01') AS running_ 展开全文

早起的虫儿e

发表于 2021-04-09 17:13:45

题目描述：按照salary的累计和running_total，其中running_total为前N个当前( to_date = '9999-01-01')员工的salary累计和，其他以此类推。具体结果如下Demo展示。解答：本题考查窗口函数中使用聚合函数 SELECT emp_no,salary 展开全文

qstalking

发表于 2021-01-25 15:42:00

select emp_no ,salary ,sum(salary)over(order 展开全文

此用户名涉嫌违规

发表于 2021-03-26 12:57:05

题目描述：按照salary的累计和running_total，其中running_total为前N个当前( to_date = '9999-01-01')员工的salary累计和，其他以此类推。窗口函数（最简单的写法）： select emp_no, salary, sum(sal 展开全文

高质量搬砖人

发表于 2021-01-31 10:49:09

方法）SUM()OVER() SELECT emp_no, salary, SUM(salary)over(ORDER BY emp_no) FROM salaries WHERE to_date = '9 展开全文

Ding_123

发表于 2021-12-06 14:48:35

知识点本题有两种写法一种直接使用窗口函数，一种使用子查询函数名(column) over(选项),所有聚合函数都可以作为窗口函数子查询就是sum使用的表员工编号小于等于外层表员工编号代码窗口函数 select emp_no, salary, sum(salary) over (ord 展开全文

辰智

发表于 2021-08-07 15:39:48

普通解法（复杂解法） select s1.emp_no,s1.salary, (select sum(salary) from salaries as s2 where s2.emp_no<=s1.emp_n 展开全文

xiaohuanxiong

发表于 2021-08-22 15:00:52

这道题考察对于联合查询的掌握程度：一般而言，我们使用联表查询时都是取等值关系进行联合，但联表实际上可以有多种联合的方式，比如这道题使用的是自连接，联合方式为s1.emp_no >= s2.emp_no，也即是s1的emp_no字段比s2.emp_no的值要大时都会联合到s1的emp_n 展开全文

牛客112736836号

发表于 2021-07-18 10:16:55

【思路一】使用窗口函数select emp_no, salary, sum(salary) over(order by emp_no) as running_total from salaries where to_date ='9999-01-01【思路二】参照评论区大佬的写法，逐条比较计算run 展开全文

问题信息

难度：

285条回答 3190收藏 19169浏览

通过挑战的用户

查看代码

牛客51982...

2022-10-19 11:29:32
牛客79445...

2022-09-29 15:46:27
牛客82325...

2022-09-16 17:03:02
牛客65067...

2022-09-16 16:42:24
healer1994

2022-09-16 11:06:01

相关试题

统计salary的累计和running_total

扫描二维码，关注牛客网
意见反馈
下载牛客APP，随时随地刷题

扫一扫，把题目装进口袋

求职之前，先上牛客: 扫描二维码，进入QQ群



扫描二维码，关注牛客公众号

公司地址：北京市朝阳区北苑路北美国际商务中心K1座一层-北京牛客科技有限公司
联系方式：010-60728802 投诉举报电话：010-57596212（朝阳人力社保局）
京ICP备14055008号-4 增值电信业务经营许可证营业执照人力资源服务许可证
京公网安备 11010502036488号

drop table if exists  `salaries` ; 
CREATE TABLE `salaries` (
`emp_no` int(11) NOT NULL,
`salary` int(11) NOT NULL,
`from_date` date NOT NULL,
`to_date` date NOT NULL,
PRIMARY KEY (`emp_no`,`from_date`));
INSERT INTO salaries VALUES(10001,60117,'1986-06-26','1987-06-26');
INSERT INTO salaries VALUES(10001,62102,'1987-06-26','1988-06-25');
INSERT INTO salaries VALUES(10001,66074,'1988-06-25','1989-06-25');
INSERT INTO salaries VALUES(10001,66596,'1989-06-25','1990-06-25');
INSERT INTO salaries VALUES(10001,66961,'1990-06-25','1991-06-25');
INSERT INTO salaries VALUES(10001,71046,'1991-06-25','1992-06-24');
INSERT INTO salaries VALUES(10001,74333,'1992-06-24','1993-06-24');
INSERT INTO salaries VALUES(10001,75286,'1993-06-24','1994-06-24');
INSERT INTO salaries VALUES(10001,75994,'1994-06-24','1995-06-24');
INSERT INTO salaries VALUES(10001,76884,'1995-06-24','1996-06-23');
INSERT INTO salaries VALUES(10001,80013,'1996-06-23','1997-06-23');
INSERT INTO salaries VALUES(10001,81025,'1997-06-23','1998-06-23');
INSERT INTO salaries VALUES(10001,81097,'1998-06-23','1999-06-23');
INSERT INTO salaries VALUES(10001,84917,'1999-06-23','2000-06-22');
INSERT INTO salaries VALUES(10001,85112,'2000-06-22','2001-06-22');
INSERT INTO salaries VALUES(10001,85097,'2001-06-22','2002-06-22');
INSERT INTO salaries VALUES(10001,88958,'2002-06-22','9999-01-01');
INSERT INTO salaries VALUES(10002,72527,'1996-08-03','1997-08-03');
INSERT INTO salaries VALUES(10002,72527,'1997-08-03','1998-08-03');
INSERT INTO salaries VALUES(10002,72527,'1998-08-03','1999-08-03');
INSERT INTO salaries VALUES(10002,72527,'1999-08-03','2000-08-02');
INSERT INTO salaries VALUES(10002,72527,'2000-08-02','2001-08-02');
INSERT INTO salaries VALUES(10002,72527,'2001-08-02','9999-01-01');
INSERT INTO salaries VALUES(10003,40006,'1995-12-03','1996-12-02');
INSERT INTO salaries VALUES(10003,43616,'1996-12-02','1997-12-02');
INSERT INTO salaries VALUES(10003,43466,'1997-12-02','1998-12-02');
INSERT INTO salaries VALUES(10003,43636,'1998-12-02','1999-12-02');
INSERT INTO salaries VALUES(10003,43478,'1999-12-02','2000-12-01');
INSERT INTO salaries VALUES(10003,43699,'2000-12-01','2001-12-01');
INSERT INTO salaries VALUES(10003,43311,'2001-12-01','9999-01-01');

统计salary的累计和running_total

输入

输出

问题信息

热门推荐

通过挑战的用户

相关试题