要求:请你编写 SQL 语句,对于每个员工,查询他除最近一个月(即最大月)之外,剩下每个月的近三个月的累计薪水(不足三个月也要计算),结果请按 Id 升序,然后按 Month 降序显示。
Employee 表:
| Id | Month | Salary |
|----|-------|--------|
| 1 | 1 | 20 |
| 2 | 1 | 20 |
| 1 | 2 | 30 |
| 2 | 2 | 30 |
| 3 | 2 | 40 |
| 1 | 3 | 40 |
| 3 | 3 | 60 |
| 1 | 4 | 60 |
| 3 | 4 | 70 |
Employee 表保存了一年内的薪水信息
Result Table:
| Id | Month | Salary |
|----|-------|--------|
| 1 | 3 | 90 |
| 1 | 2 | 50 |
| 1 | 1 | 20 |
| 2 | 1 | 20 |
| 3 | 3 | 100 |
| 3 | 2 | 40 |
分析:
1.求每三个月的累计工资,因此需要使用窗口聚合函数sum() over()去求取当前月和下方两个月的工资之和
2.查询结果去除最大月使用窗口函数row_number() over()对month降序排序,去除最大的月即可
SQL语句:
SELECT t.id,t.month,t.s1 AS Salary
FROM (
SELECT id,month,salary,
SUM(salary) over(PARTITION BY id ORDER BY month DESC
ROWS BETWEEN current ROW AND 2 following) AS s1,
row_number() over(PARTITION BY id ORDER BY month DESC )AS r1
FROM
Employee
)t
WHERE r1>1;