有2张表表AXH DZDAH RQ
111 11111111 2008-01-02
112 11111111 2008-09-22
113 11111111 2008-04-11
114 22222222 2009-01-02
115 22222222 2009-03-02
116 33333333 2008-02-02表BXH JE
111 100
112 299
113 222
114 300
115 211
116 322 需要结果为每个DZDAH中最后更新日期的JE数,盼回复!
DZDAH JE rq
11111111 299 2008-09-22
22222222 300 2009-03-02
33333333 322 2008-02-02
111 11111111 2008-01-02
112 11111111 2008-09-22
113 11111111 2008-04-11
114 22222222 2009-01-02
115 22222222 2009-03-02
116 33333333 2008-02-02表BXH JE
111 100
112 299
113 222
114 300
115 211
116 322 需要结果为每个DZDAH中最后更新日期的JE数,盼回复!
DZDAH JE rq
11111111 299 2008-09-22
22222222 300 2009-03-02
33333333 322 2008-02-02
insert into A select '111', '11111111' , to_date('2008-01-02','yyyy-MM-dd') from dual;
insert into A select '112', '11111111', to_date('2008-09-22','yyyy-MM-dd') from dual;
insert into A select '113' , '11111111' , to_date('2008-04-11','yyyy-MM-dd') from dual;
insert into A select '114' , '22222222' , to_date('2009-01-02','yyyy-MM-dd') from dual;
insert into A select '115' , '22222222' , to_date('2009-03-02 ','yyyy-MM-dd') from dual;
insert into A select '116' , '33333333' , to_date('2008-02-02','yyyy-MM-dd') from dual;
commit;
create table B(XH varchar2(20), JE varchar2(20));
insert into B select '111', '100' from dual;
insert into B select '112','299 ' from dual;
insert into B select '113', '222' from dual;
insert into B select '114', '300' from dual;
insert into B select '115', '211' from dual;
insert into B select '116', '322' from dual;
commit;select a2.DZDAH,B.JE,a2.RQ
from(
select a1.DZDAH,(select A.XH from A where A.DZDAH=a1.DZDAH and A.RQ=a1.RQ) as XH, a1.RQ from
(select DZDAH,max(RQ) as RQ from A group by DZDAH)a1
)a2,B
where B.XH=a2.XH--------------------------
DZDAH JE RQ
11111111 299 2008-09-22
22222222 211 2009-03-02
33333333 322 2008-02-02[/Quote]
你提供的结果有误,不是:
DZDAH JE rq
11111111 299 2008-09-22
22222222 300 2009-03-02
33333333 322 2008-02-02 而是DZDAH JE rq
11111111 299 2008-09-22
22222222 211 2009-03-02
33333333 322 2008-02-02