首页 > 试题广场 >

统计salary的累计和running_total

[编程题]统计salary的累计和running_total
  • 热度指数：118904 时间限制：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
算法知识视频讲解
添加回答
ciphersaw头像 ciphersaw
本题的思路为复用 salaries 表进行子查询，最后以 s1.emp_no 排序输出求和结果。
1、输出的第三个字段，是由一个 SELECT 子查询构成。将子查询内复用的 salaries 表记为 s2，主查询的 salaries 表记为 s1，当主查询的 s1.emp_no 确定时，对子查询中不大于 s1.emp_no 的 s2.emp_no 所对应的薪水求和
2、注意是对员工当前的薪水求和，所以在主查询和子查询内都要加限定条件 to_date = '9999-01-01' 
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_total 
FROM salaries AS s1 WHERE s1.to_date = '9999-01-01' ORDER BY s1.emp_no

编辑于 2017-10-07 10:24:26 回复(27)
更多回答
Stzolo头像 Stzolo
# 本题思路：
求所有（to_date = '9999-01-01'）员工的累加和，也就是在职员工的累加和
为了应对答案顺序避免出错，所以加了order by 来将工号进行排序（其实无关紧要）
利用定义变量@m，来进行赋值累加。累加结果就是前面所有员工薪资+本员工的薪资。
最后至于为什么用cast(COALESCE()  AS DECIMAL (18, 0)) 转换类型，其实是变量进行的累加和会有小数位（真的奇怪），利用round等函数还去不掉，所以只能进行了类型并去掉小数位。
# set @m := 0;
select emp_no, salary, cast(COALESCE(@m := salary + @m)  AS DECIMAL (18, 0)) as running_total 
from salaries, (select @m := 0) a
where to_date = '9999-01-01'
order by emp_no
发表于 2022-04-17 20:56:50 回复(0)
Mysql
牛客886597046号头像 牛客886597046号
select emp_no,salary,(select sum(tmp.salary) from salaries tmp where tmp.emp_no<s.emp_no and  tmp.to_date = '9999-01-01') as running_total
from salaries s 
发表于 2022-01-09 17:50:39 回复(1)
夏季smile头像 夏季smile
这道题还是用窗口函数比较简单哦~
SELECT emp_no,salary,
SUM(salary) OVER (ORDER BY emp_no) AS running_total
FROM salaries
WHERE to_date = '9999-01-01';

发表于 2019-08-13 16:16:10 回复(11)
DBwater头像 DBwater 
把所有小于等于当前编号的表s1和当前编号表s2联立起来，然后按照当前编号分组，计算出所有小于等于
当前标号的工资总数
SELECT s2.emp_no,s2.salary,SUM(s1.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 s2.emp_no

发表于 2017-08-03 22:35:58 回复(10)
C＃ぞ‖头像 C＃ぞ‖
题干表述应该是有问题，running_total给出的应该是前面所有员工的salary之和。 
select a.emp_no, a.salary, sum(b.salary)
from salaries as a, salaries as b
where b.emp_no <= a.emp_no
and a.to_date = '9999-01-01'
and b.to_date = '9999-01-01'
group by a.emp_no
order by a.emp_no asc

发表于 2017-08-17 10:18:47 回复(13)
秋夜童话DQYer头像 秋夜童话DQYer 
select emp_no,salary,
sum(salary) over(order by emp_no) as running_total
from salaries
where to_date= '9999-01-01';
解题思路：
①本题关键在于把sum聚合函数作为窗口函数使用，所有聚合函数都能用做窗口函数，其语法和专用窗口函数完全相同。
sum(<汇总列>) over(<排序列>) as 别名；
②光看题目“前两个员工的salary累计和”不是很好理解，结合输出格式可以理解为running_total列是逐个员工的工资的累计和，每一行的工资都是前面所有行的工资总计。
③这有一个小bug，题目没有限定时间为当前，而按照输出格式来看和通过情况来看，只有限定时间为当前'9999-01-01'才能符合输出格式，才能通过，一开始考虑用员工分组，但是员工分组得到的结果并非题目本意，必须限定时间为当前。
发表于 2019-12-02 08:53:46 回复(0)
Mysql
牛客106248132号头像 牛客106248132号
窗口函数 
select emp_no,salary,sum(salary) over(order by emp_no)
from salaries
where to_date='9999-01-01'
不用窗口函数，用自定义变量 
select t3.emp_no,t3.salary,round(t3.sumsa)--但是不知道什么原因，round在这里没作用
from
(
select t1.emp_no,t1.salary,@sum:=@sum+t1.salary sumsa
from salaries t1,(select @sum:=0) t2
where t1.to_date='9999-01-01') t3
不用自定义变量
SELECT t1.emp_no,t1.salary,sum(t2.salary)
from salaries t1,salaries t2
where t1.to_date='9999-01-01' and t2.to_date='9999-01-01' and t1.emp_no>=t2.emp_no
group by t1.emp_no


发表于 2021-10-13 10:27:30 回复(0)
快递好助手头像 快递好助手
题目有问题 这不是前两个员工的和 而是之前所有员工的和
发表于 2017-07-27 15:43:51 回复(2)
bellz头像 bellz
加上to_date的条件是因为，一个人的工资可能有多条记录，9999-01-01对应的记录则是最新的记录。
写法3其实比较直观，就是用一个子查询，来计算当前a.emp_no的累计薪资。最难以置信的是写法2，join on 后面的表达式居然也可以不是等式（其实就等价于写法1，只是规定了join的类型）
疑惑点：为什么是group by a.emp_no？（据说是因为，一个a.emp_no需要有一条sum的记录，所以是按照emp_no来聚合）

写法1：
select a.emp_no, a.salary, sum(b.salary) as running_total 
from salaries a, salaries b
where b.emp_no <= a.emp_no
and a.to_date = '9999-01-01'
and b.to_date = '9999-01-01'
group by a.emp_no

写法2：
select a.emp_no, a.salary, sum(b.salary) as running_total 
from salaries a inner join salaries b
on b.emp_no <= a.emp_no
where a.to_date = '9999-01-01'
and b.to_date = '9999-01-01'
group by a.emp_no

写法3：
select a.emp_no, a.salary, 
(select sum(b.salary) from salaries b 
 where b.to_date = '9999-01-01' 
 and b.emp_no <= a.emp_no)
as running_total 
from salaries a
where a.to_date = '9999-01-01'
order by a.emp_no

发表于 2019-06-17 17:15:01 回复(1)
指offer的剑在哪？头像 指offer的剑在哪？
看了标准答案之后，很是无语，不知道为什么需要添加where to_date="9999-01-01"，评论区有大神说是因为计算的是员工的当前薪水，所以需要加上这个条件，还是有点搞不懂
发表于 2017-09-03 22:00:08 回复(5)
Sqlite
牛客444760745号头像 牛客444760745号

窗口函数即可

SELECT emp_no, salary, sum(salary) over(order by emp_no) AS running_total
FROM salaries
WHERE to_date='9999-01-01';
发表于 2021-10-06 19:51:46 回复(0)
Mysql
牛客477450903号头像 牛客477450903号
SELECT emp_no,salary,sum(salary) over(order by emp_no) running_total
from salaries
where to_date='9999-01-01'
发表于 2021-09-08 11:24:39 回复(0)
Mysql
惠灵顿靖头像 惠灵顿靖 
select emp_no,salary,sum(salary) over(order by emp_no) running_total
from salaries 
where to_date="9999-01-01"
# sum(col1) over(partition by col2 order by col3 )
# 以上的函数可以理解为：按col2 进行分组(partition )，每组以col3 进行排序(order)，并进行连续加总（sum）

发表于 2021-10-04 16:24:57 回复(0)
Mysql
leomax1993头像 leomax1993
这个题目有意思么，数清楚点会死，出题的非把自己玩的这么玄乎么
发表于 2021-07-30 19:43:42 回复(0)
Mysql
牛客184739658号头像 牛客184739658号 
-- 方法一：子查询法
select s1.emp_no
,s1.salary
,
(
    select sum(s2.salary) from salaries s2
    where s2.emp_no <= s1.emp_no -- 把小于等于的都加起来就是了
    and s2.to_date = '9999-01-01'
) running_total
from salaries s1
where s1.to_date = '9999-01-01'
order by s1.emp_no

-- 方法二：自连接法
select s1.emp_no
,s1.salary
,sum(s2.salary) running_total
from salaries s1
join salaries 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
order by s1.emp_no

-- 方法三：窗口函数
select emp_no
,salary
,sum(salary)over(order by emp_no) running_total
from salaries
where to_date = '9999-01-01'

发表于 2021-07-21 10:21:18 回复(1)
BWTNjIrMFoan头像 BWTNjIrMFoan
考点：动态获取前n个数据
方法一：窗口函数 
select emp_no, salary, sum(salary) over (order by emp_no) running_total
from salaries
where to_date = '9999-01-01'
方法二：子查询 
select T.emp_no, T.salary, sum(T2.salary)
from salaries T 
join salaries T2 on (T2.emp_no <= T.emp_no and T2.to_date = '9999-01-01') 
where T.to_date = '9999-01-01'
group by T.emp_no
order by T.emp_no



发表于 2021-03-27 17:30:58 回复(1)
🍅煜201910092146815头像 🍅煜201910092146815
第一反应是典型的窗口函数应用：
https://zhuanlan.zhihu.com/p/92654574 --窗口函数简单解释
select 
emp_no,
salary,
sum(salary) over(order by emp_no) as running_total
from salaries 
where  to_date='9999-01-01'


第二方法是关联比表1编号小的表2后 ，表2salary相加： 
select 
s1.emp_no,
s1.salary,
sum(s2.salary) running_total
from salaries s1
join salaries 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
order by s1.emp_no
还有第三种方法在其他题目里刷到的自定义变量的，我还不熟练，参考其他同学解法
发表于 2020-07-23 23:11:58 回复(0)
黄宗文头像 黄宗文
-- sum(salary) over(order by emp_no)
-- order by 不能省略，不然每一个都显示所有数据的和
select emp_no, salary, sum(salary) over(order by emp_no) as running_total
from salaries
where to_date='9999-01-01'
;
发表于 2020-07-17 17:03:10 回复(0)
牛客504792140号头像 牛客504792140号
使用自连接，思路类似于查询排名情况：
select s1.emp_no, s1.salary, sum(s2.salary) as running_total
from salaries as s1
join salaries as s2 on s1.emp_no >= s2.emp_no
and s1.to_date = '9999-01-01'
and s2.to_date = '9999-01-01'
group by s1.emp_no;
发表于 2020-05-18 00:37:01 回复(0)
提交观点

问题信息

难度：
264条回答 18907浏览

热门推荐

通过挑战的用户

查看代码

相关试题

扫一扫，把题目装进口袋

求职之前，先上牛客

扫描二维码，进入QQ群

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