ORACLE 分析函数

--  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 新增