-- lead lag over 分析函数 注意的地方
with temp as
(select '张三' name, '8-20' date1, 1000 a1, 1000 a2, 5 a3
from dual
union all
select '李四' name, '8-20' date1, 1100 a1, 900 a2, 10 a3
from dual
union all
select '张三' name, '9-20' date1, 900 a1, 1000 a2, 10 a3
from dual
union all
select '李四' name, '9-20' date1, 1100 a1, 900 a2, 13 a3
from dual
union all
select '王五' name, '9-20' date1, 900 a1, 100 a2, 0 a3
from dual)
select name "姓名", c "基本工资", cnt,decode(cnt, 1, '新增', '') "备注"
from (select name,
a1,
date1,
a1 -
(lead(a1, 1, 0) over(partition by name order by date1 desc)) c,
row_number() over(partition by name order by date1 desc) rn,
sum(1) over(partition by name ) cnt
from temp)
where rn = 1
and c <> 0;
-- 最终效果
序号 姓名 基本工资 备注
1 张三 -100
2 王五 9001 新增