建表
DROP TABLE IF EXISTS employee;CREATE TABLE employee(Id INT,MONTH INT,Salary DECIMAL);INSERT INTO employee VALUES(1, 1, 20);INSERT INTO employee VALUES(2, 1, 20);INSERT INTO employee VALUES(1, 2, 30);INSERT INTO employee VALUES(2, 2, 30);INSERT INTO employee VALUES(3, 2, 40);INSERT INTO employee VALUES(1, 3, 40);INSERT INTO employee VALUES(3, 3, 60);INSERT INTO employee VALUES(1, 4, 60);INSERT INTO employee VALUES(3, 4, 70);
SELECT t.id, t.`month`, t.salary FROM( SELECT id, `month`, SUM(salary) over(PARTITION BY id ORDER BY `month` ROWS 2 PRECEDING) salary, -- 累加的总薪水 rank() over(PARTITION BY id ORDER BY `month` DESC) ranks -- 排名好去掉最近一个月 FROM employee) tWHERE t.ranks > 1
上期推文:
LeetCode1-560题汇总,希望对你有点帮助!
LeetCode刷题实战561:数组拆分 I
LeetCode刷题实战562:矩阵中最长的连续1线段
LeetCode刷题实战563:二叉树的坡度
LeetCode刷题实战564:寻找最近的回文数
LeetCode刷题实战565:数组嵌套
LeetCode刷题实战566:重塑矩阵
LeetCode刷题实战567:字符串的排列
LeetCode刷题实战568:最大休假天数
LeetCode刷题实战569:员工薪水中位数
LeetCode刷题实战570:至少有5名直接下属的经理
LeetCode刷题实战571:给定数字的频率查询中位数
LeetCode刷题实战572:另一棵树的子树
LeetCode刷题实战573:松鼠模拟
LeetCode刷题实战574:当选者
LeetCode刷题实战575:分糖果
LeetCode刷题实战576:出界的路径数
LeetCode刷题实战577:员工奖金
LeetCode刷题实战578:查询回答率最高的问题
`单行代码`