create table t1 (id number(5),y_id number(5),y_my number(10,2),t_date date);insert into t1 values (1,1,1000,date'2012-01-01'); insert into t1 values (2,2,2000,date'2012-01-01'); insert into t1 values (3,3,3000,date'2012-01-01'); insert into t1 values (4,1,1500,date'2012-02-01'); insert into t1 values (5,2,2500,date'2012-02-01'); insert into t1 values (6,3,3500,date'2012-02-01'); insert into t1 values (7,1,1100,date'2012-03-01'); insert into t1 values (8,3,2200,date'2012-03-01'); insert into t1 values (9,4,1000,date'2012-03-01'); commit;--查询某个员工最后一次记录员 select * from ( select * from t1 where y_id = 1 order by t_date desc ) where rownum < 2 --查询所有员工最后一次记录员 select tt.id,tt.y_id,tt.y_my,tt.t_date from ( select a.*,row_number() over(partition by y_id order by t_date desc) rn from t1 a ) tt where rn = 1
关联员工表就可以了create table t1 (id number(5),y_id number(5),y_my number(10,2),t_date date);insert into t1 values (1,1,1000,date'2012-01-01'); insert into t1 values (2,2,2000,date'2012-01-01'); insert into t1 values (3,3,3000,date'2012-01-01'); insert into t1 values (4,1,1500,date'2012-02-01'); insert into t1 values (5,2,2500,date'2012-02-01'); insert into t1 values (6,3,3500,date'2012-02-01'); insert into t1 values (7,1,1100,date'2012-03-01'); insert into t1 values (8,3,2200,date'2012-03-01'); insert into t1 values (9,4,1000,date'2012-03-01');create table t2 (y_id number(5),y_name varchar2(10)); insert into t2 values (1,'安安'); insert into t2 values (2,'豆豆'); insert into t2 values (3,'丢丢'); insert into t2 values (4,'顶顶'); insert into t2 values (5,'宝宝'); commit; select y_name,y_my,t_date from ( select a.y_name,b.y_my,b.t_date, row_number() over(partition by b.y_id order by b.t_date desc) rn from t2 a left join t1 b on a.y_id=b.y_id ) where rn = 1 y_name y_my t_date --------------------------------- 1 安安 1100.00 2012/3/1 2 豆豆 2500.00 2012/2/1 3 丢丢 2200.00 2012/3/1 4 顶顶 1000.00 2012/3/1 5 宝宝
编写存储过程封装
插入和查询上一个养老金的SQL语句
输入的内容作为存入参数
create table t1 (id number(5),y_id number(5),y_my number(10,2),t_date date);insert into t1 values (1,1,1000,date'2012-01-01');
insert into t1 values (2,2,2000,date'2012-01-01');
insert into t1 values (3,3,3000,date'2012-01-01');
insert into t1 values (4,1,1500,date'2012-02-01');
insert into t1 values (5,2,2500,date'2012-02-01');
insert into t1 values (6,3,3500,date'2012-02-01');
insert into t1 values (7,1,1100,date'2012-03-01');
insert into t1 values (8,3,2200,date'2012-03-01');
insert into t1 values (9,4,1000,date'2012-03-01');
commit;--查询某个员工最后一次记录员
select *
from
(
select *
from t1
where y_id = 1
order by t_date desc
)
where rownum < 2 --查询所有员工最后一次记录员
select tt.id,tt.y_id,tt.y_my,tt.t_date
from
(
select a.*,row_number() over(partition by y_id order by t_date desc) rn
from t1 a
) tt
where rn = 1
insert into t1 values (2,2,2000,date'2012-01-01');
insert into t1 values (3,3,3000,date'2012-01-01');
insert into t1 values (4,1,1500,date'2012-02-01');
insert into t1 values (5,2,2500,date'2012-02-01');
insert into t1 values (6,3,3500,date'2012-02-01');
insert into t1 values (7,1,1100,date'2012-03-01');
insert into t1 values (8,3,2200,date'2012-03-01');
insert into t1 values (9,4,1000,date'2012-03-01');create table t2 (y_id number(5),y_name varchar2(10));
insert into t2 values (1,'安安');
insert into t2 values (2,'豆豆');
insert into t2 values (3,'丢丢');
insert into t2 values (4,'顶顶');
insert into t2 values (5,'宝宝');
commit;
select y_name,y_my,t_date
from
(
select a.y_name,b.y_my,b.t_date,
row_number() over(partition by b.y_id order by b.t_date desc) rn
from t2 a left join t1 b on a.y_id=b.y_id
)
where rn = 1 y_name y_my t_date
---------------------------------
1 安安 1100.00 2012/3/1
2 豆豆 2500.00 2012/2/1
3 丢丢 2200.00 2012/3/1
4 顶顶 1000.00 2012/3/1
5 宝宝