有A表 其中两个字段SRecord_ID Scount(数量)
还有个历史表A_his有三个字段 record_id,activate_id,operate_date
历史表里的activate_id和A表中的Record_ID相同,A表里的一条记录可能对应历史表里的多条记录现在要查A_his.record_id A.Scount 数量,A_his.operate_date 日期
因为历史表对应了多条记录 所以日期要显示最早的日期
还有个历史表A_his有三个字段 record_id,activate_id,operate_date
历史表里的activate_id和A表中的Record_ID相同,A表里的一条记录可能对应历史表里的多条记录现在要查A_his.record_id A.Scount 数量,A_his.operate_date 日期
因为历史表对应了多条记录 所以日期要显示最早的日期
select record_id,数量,日期 from
(select A_his.record_id,A.Scount 数量,A_his.operate_date 日期,
ROW_NUMBER() OVER (PARTITION BY a.Record_ID ORDER BY A_his.operate_date) AS rn
from a_his,a
where a_his.activate_id=a.Record_ID)
where rn=1
from A,
(
select record_id,activate_id,operate_date
from(
select record_id,activate_id,operate_date,row_number() over(partition by activate_id order by operate_date asc) rn from A_his
) where rn=1) B
where a.SRecord_ID=b.activate_id
SRecord_ID number,
Scount number
)
create table t2(
record_id number,
activate_id number,
operate_date date
)
insert into t1(SRecord_ID,Scount) values(1,30);
insert into t1(SRecord_ID,Scount) values(2,20);
insert into t1(SRecord_ID,Scount) values(3,10);
insert into t2 values(1,1,sysdate);
insert into t2 values(2,1,sysdate-1);
insert into t2 values(3,1,sysdate-2);
insert into t2 values(4,2,sysdate-5);
insert into t2 values(5,2,sysdate-6);
select
tt.record_id,t1.Scount,tt.operate_date
from t1,
(select
record_id, activate_id,operate_date,
row_number() over(partition by ACTIVATE_ID order by operate_date) as rowno
from t2) tt
where
t1.SRecord_ID = tt.activate_id and
tt.rowno = 1;结果:
RECORD_ID SCOUNT OPERATE_DATE
---------- ---------- ------------
3 30 2009-6-28 下午
5 20 2009-6-24 下午
group by就可以了,也可以Join 之后order by 日期 desc and rownum=1
from A,
(
select record_id,activate_id,operate_date
from(
select record_id,activate_id,operate_date,row_number() over(partition by activate_id order by operate_date asc) rn from A_his
) where rn=1) B
where a.SRecord_ID=b.activate_id
from a,
(select SRecord_ID, min(operate_date) from A_hist group by SRecord_ID) b
where a.SRecord_ID = b.SRecord_ID;