首页 > 试题广场 >

统计salary的累计和running_total

[编程题]统计salary的累计和running_total
  • 热度指数：119412 时间限制：C/C++ 1秒，其他语言2秒 空间限制：C/C++ 32M，其他语言64M
  • 算法知识视频讲解
按照salary的累计和running_total，其中running_total为前两个员工的salary累计和，其他以此类推。 具体结果如下Demo展示。。
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');

输出

10001|88958|88958
10002|72527|161485
10003|43311|204796
算法知识视频讲解
添加回答
Mysql
牛客462530318号头像 牛客462530318号
select
emp_no,
salary,
sum(salary) over(order by emp_no) as running_total
from  salaries where to_date='9999-01-01'
发表于 2024-05-29 10:25:59 回复(0)
Mysql
翻斗花园第一狙击手头像 翻斗花园第一狙击手 
select emp_no,salary,sum(t.salary) over(order by emp_no asc)as running_total 
from (select emp_no,salary
    from salaries
    where to_date = '9999-01-01'
) t

发表于 2024-04-22 17:05:09 回复(0)
Mysql
FMW头像 FMW 
SELECT
    emp_no,
    salary,
    sum(salary) OVER(ORDER BY emp_no) AS running_total
FROM salaries 
WHERE to_date = '9999-01-01'
GROUP BY emp_no, salary;

发表于 2024-04-03 20:45:03 回复(0)
Mysql
弱点保险鬼蝉头像 弱点保险鬼蝉
select emp_no,salary,sum(salary) over(order by emp_no)
from salaries
where to_date = "9999-01-01";
发表于 2024-03-07 10:06:06 回复(0)
Mysql
一只小财务头像 一只小财务
有大佬帮忙看下这个错在哪里吗
select emp_no,salary
case when emp_no ='10001' then salary*1
else salary + lead(salary,1)over(order by emp_no)
end as running_total
from salaries
where to_date='9999-01-01'
order by emp_no asc
发表于 2024-02-22 16:14:05 回复(0)
Mysql
晓晓程序猿头像 晓晓程序猿
在不支持窗口函数时的解法：
select s1.emp_no,s1.salary,sum(s2.salary) as running_total from salaries s1,salaries s2
where s1.to_date = '9999-01-01' and s2.to_date = '9999-01-01' and s1.emp_no >= s2.emp_no
group by s1.emp_no,s1.salary
发表于 2024-01-19 21:31:58 回复(0)
Mysql
JoyceFang_077头像 JoyceFang_077
妈呀这题目描述的是不是有点不清楚，还以为是求在职员工一直以来的薪水合，实际上是求按照id排序的薪水和，就是说第一个员工就是自己的薪水，那第二个员工是第一个员工薪水加上自己的……
实际输出
10001
88958
88958
10002
72527
161485（注 72527+88958）
10003
43311
204796（注 43311+72527+88958）
学到的新知识点是，原来联结后面的条件还可以是不等式
SELECT s1.emp_no,s1.salary,SUM(s2.salary) AS running_total
FROM salaries AS s1
INNER JOIN salaries AS s2
ON s1.emp_no>=s2.emp_no
WHERE s1.to_date='9999-01-01'
AND s2.to_date='9999-01-01'
GROUP BY s1.emp_no,s1.salary;

发表于 2023-10-12 19:30:42 回复(0)
Mysql
黄革乔头像 黄革乔
我的是mysql5.7，用不了开窗函数，所以用下面这个办法：

set @running_total=0;

select emp_no,salary,@running_total:=@running_total+salary running_total

from salaries s

where to_date ='9999-01-01'

order by emp_no

发表于 2023-08-25 16:18:07 回复(0)
Mysql
粗心的小猫想开了头像 粗心的小猫想开了 
select
    t1.emp_no,
    t1.salary,
    sum(salary) over (
        order by
            emp_no asc rows unbounded preceding
    ) running_total
from
    salaries t1
where
    t1.to_date = '9999-01-01';

发表于 2023-05-21 11:11:42 回复(0)
Mysql
我叫谢小盟，叫我查尔斯好了头像 我叫谢小盟，叫我查尔斯好了 
#4

select emp_no, salary, sum(salary)over(order by emp_no asc) as running_total
from salaries
where to_date = "9999-01-01"

发表于 2023-02-28 01:19:13 回复(0)
Mysql
小宏的大牛之路头像 小宏的大牛之路 
select emp_no,salary,
sum(salary) over(order by rn) as running_total
from (
   select emp_no,salary,
   row_number() over(order by emp_no) as rn
   from salaries
   where to_date = '9999-01-01'
)t;

发表于 2022-12-28 14:30:39 回复(0)
Mysql
一颗宏心头像 一颗宏心 
select emp_no,salary,
sum(salary) over(order by emp_no) as running_total
from salaries
where to_date = '9999-01-01';
我也会窗口函数了

发表于 2022-11-06 20:44:00 回复(0)
Mysql
oooook_ok头像 oooook_ok 
with tem as (select *, row_number()over(order by emp_no) as rn from salaries
            where to_date = '9999-01-01')

select 
    t1.emp_no,
    t1.salary,
    sum(t2.salary)
from tem t1 
join tem t2
on t1.emp_no >= t2.emp_no
group by t1.emp_no, t1.salary
order by t1.emp_no

发表于 2022-07-30 12:18:07 回复(0)
Mysql
来牛客玩玩头像 来牛客玩玩 
select emp_no, salary, sum(salary) over (order by emp_no) as running_total from salaries
where to_date = '9999-01-01';

发表于 2022-06-26 09:09:20 回复(0)
Mysql
买飞飞也用券头像 买飞飞也用券 
select emp_no,salary,sum(salary)over(order by emp_no) running_total from salaries 
where to_date='9999-01-01'

此处因为over()中使用了order by子句，
所以默认从第一行累计到当前行；
若不使用order by子句，则会针对整个分区求和（此处没指定partition by，所以将整个表视为一个分区）
发表于 2022-06-11 17:37:38 回复(0)
Mysql
圣光·明流火头像 圣光·明流火
select emp_no ,salary,sum(salary) over (order by emp_no) running_total
from salaries
where to_date = "9999-01-01";
发表于 2022-06-07 14:45:23 回复(0)
Mysql
牛客535290159号头像 牛客535290159号
select a.*,sum(a.salary) over (order by a.emp_no asc) as running_total from 
(select emp_no,salary from salaries where to_date ='9999-01-01' ) as a
发表于 2022-05-20 20:25:11 回复(0)
Mysql
赵木枝头像 赵木枝
窗口函数真好用啊，怠惰 
select emp_no,salary,sum(salary) over(order by emp_no) as running_total 
from salaries
where to_date = '9999-01-01'


发表于 2022-05-19 20:05:17 回复(0)
提交观点

问题信息

难度：
66条回答 18919浏览

热门推荐

通过挑战的用户

查看代码

相关试题

扫一扫，把题目装进口袋

求职之前，先上牛客

扫描二维码，进入QQ群

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