SQL练习:SQL60_较难
题目: 按照salary的累计和running_total,其中running_total为前N个当前( to_date = ‘9999-01-01’)员工的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 |
题解:
# 方式1:窗口函数
SELECT emp_no,salary,
SUM(salary) OVER (ORDER BY emp_no) AS running_total # 聚合函数(SUM等)可以用做窗口函数
FROM salaries
WHERE to_date = '9999-01-01';
# 方式2:自连接
SELECT A.emp_no, A.salary, SUM(B.salary)
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
ORDER BY A.emp_no
# 方式3:子查询
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
- 个人整理的笔记,仅供学习使用,有问题麻烦指正。
- 题目来源于:牛客网