做薪资查询的时候出现问题,看下面的数据表:
select * from t_user ORDER BY UserID ASC, date ASC ;
结果如下:
UserID paySum date Because
1 1000 2001-01-01 入职
1 2000 2002-01-01 加薪
1 3000 2003-01-01 加薪
2 1500 2002-01-01 升职
2 1800 2003-01-01 加薪
2 2500 2004-01-01 升职
3 1100 2000-01-01 入职
3 1500 2002-01-01 加薪我希望得出的结果如下:
UserID paySum oldPaySum date Because
1 1000 0 2001-01-01 入职
1 2000 1000 2002-01-01 加薪
1 3000 2000 2003-01-01 加薪
2 1500 0 2002-01-01 升职
2 1800 1500 2003-01-01 加薪
2 2500 1800 2004-01-01 升职
3 1100 0 2000-01-01 入职
3 1500 1100 2002-01-01 加薪解释如下:
希望在查询每个人工资的时候可以查询出他上一次的工资并且一同显示出来;
如果他上一次的工资没有记录(比如他刚入职),则设为0.
要求:
不用存储过程。
大家有什么高见,智者见智,欢迎参与!
select * from t_user ORDER BY UserID ASC, date ASC ;
结果如下:
UserID paySum date Because
1 1000 2001-01-01 入职
1 2000 2002-01-01 加薪
1 3000 2003-01-01 加薪
2 1500 2002-01-01 升职
2 1800 2003-01-01 加薪
2 2500 2004-01-01 升职
3 1100 2000-01-01 入职
3 1500 2002-01-01 加薪我希望得出的结果如下:
UserID paySum oldPaySum date Because
1 1000 0 2001-01-01 入职
1 2000 1000 2002-01-01 加薪
1 3000 2000 2003-01-01 加薪
2 1500 0 2002-01-01 升职
2 1800 1500 2003-01-01 加薪
2 2500 1800 2004-01-01 升职
3 1100 0 2000-01-01 入职
3 1500 1100 2002-01-01 加薪解释如下:
希望在查询每个人工资的时候可以查询出他上一次的工资并且一同显示出来;
如果他上一次的工资没有记录(比如他刚入职),则设为0.
要求:
不用存储过程。
大家有什么高见,智者见智,欢迎参与!
select UserID,
paySum,
lag(paySum) over(partition by UserID order by date) oldpaySum,
date,
Because
from tb
order by UserID,date
zhuomingwang
你的语句没全对,如果user的没有上一年的记录,那么今年的oldpaySum应该为0,这个该怎么做啊?
select 1 as Userid, 1000 as paySum, to_date('2001-01-01','yyyy-mm-dd') as dates, '入职' as Because from dual
union all select
1, 2000, to_date('2002-01-01','yyyy-mm-dd'), '加薪' from dual union all select
1, 3000, to_date('2003-01-01','yyyy-mm-dd'), '加薪' from dual union all select
2, 1500, to_date('2002-01-01','yyyy-mm-dd'), '升职' from dual union all select
2, 1800, to_date('2003-01-01','yyyy-mm-dd'), '加薪' from dual union all select
2, 2500, to_date('2004-01-01','yyyy-mm-dd'), '升职' from dual union all select
3, 1100, to_date('2000-01-01','yyyy-mm-dd'), '入职' from dual union all select
3, 1500, to_date('2002-01-01','yyyy-mm-dd'), '加薪' from dual),
b as(
select t1.Userid, t1.paySum, t1.dates, t1.because, row_number() over(partition by t1.Userid order by t1.dates) as od
from a t1 )
select b1.Userid, b1.paySum, nvl(b2.paySum,0) as oldPaySum, b1.dates, b1.because
from b b1 left join b b2 on b1.Userid=b2.Userid and b1.od=b2.od-1;
select UserID,
paySum,
nvl(lag(paySum) over(partition by UserID order by date),0) oldpaySum,
date,
Because
from tb
order by UserID,date
select 1 as Userid, 1000 as paySum, to_date('2001-01-01','yyyy-mm-dd') as dates, '入职' as Because from dual
union all select
1, 2000, to_date('2002-01-01','yyyy-mm-dd'), '加薪' from dual union all select
1, 3000, to_date('2003-01-01','yyyy-mm-dd'), '加薪' from dual union all select
2, 1500, to_date('2002-01-01','yyyy-mm-dd'), '升职' from dual union all select
2, 1800, to_date('2003-01-01','yyyy-mm-dd'), '加薪' from dual union all select
2, 2500, to_date('2004-01-01','yyyy-mm-dd'), '升职' from dual union all select
3, 1100, to_date('2000-01-01','yyyy-mm-dd'), '入职' from dual union all select
3, 1500, to_date('2002-01-01','yyyy-mm-dd'), '加薪' from dual)
select UserID,
paySum,
nvl(lag(paySum) over(partition by UserID order by dates),0) oldpaySum,
dates,
Because
from a
order by UserID,dates;
select 1 as Userid, 1000 as paySum, to_date('2001-01-01','yyyy-mm-dd') as dates, '入职' as Because from dual
union all select
1, 2000, to_date('2002-01-01','yyyy-mm-dd'), '加薪' from dual union all select
1, 3000, to_date('2003-01-01','yyyy-mm-dd'), '加薪' from dual union all select
2, 1500, to_date('2002-01-01','yyyy-mm-dd'), '升职' from dual union all select
2, 1800, to_date('2003-01-01','yyyy-mm-dd'), '加薪' from dual union all select
2, 2500, to_date('2004-01-01','yyyy-mm-dd'), '升职' from dual union all select
3, 1100, to_date('2000-01-01','yyyy-mm-dd'), '入职' from dual union all select
3, 1500, to_date('2002-01-01','yyyy-mm-dd'), '加薪' from dual),
b as(
select t1.Userid, t1.paySum, t1.dates, t1.because, row_number() over(partition by t1.Userid order by t1.dates) as od
from a t1 )
select b1.Userid, b1.paySum, nvl(b2.paySum,0) as oldPaySum, b1.dates, b1.because
from b b1 left join b b2 on b1.Userid=b2.Userid and b1.od=b2.od+1
order by UserId,dates;