ORACLE 9i 调试通过。
create table TEMP1
(
RDATA DATE,
RUSER VARCHAR2(10)
);
create table TEMP2
(
RDATA DATE,
RUSER VARCHAR2(10)
);
insert into TEMP1 (RDATA, RUSER)
values (to_date('05-11-2012', 'dd-mm-yyyy'), 'NEW');
insert into TEMP1 (RDATA, RUSER)
values (to_date('02-11-2012', 'dd-mm-yyyy'), 'NEW');
insert into TEMP1 (RDATA, RUSER)
values (to_date('02-11-2012', 'dd-mm-yyyy'), 'NEW');
insert into TEMP1 (RDATA, RUSER)
values (to_date('02-11-2012', 'dd-mm-yyyy'), 'NEW');
insert into TEMP1 (RDATA, RUSER)
values (to_date('01-11-2012', 'dd-mm-yyyy'), 'NEW');
insert into TEMP1 (RDATA, RUSER)
values (to_date('05-11-2012', 'dd-mm-yyyy'), 'NEW');
insert into TEMP1 (RDATA, RUSER)
values (to_date('05-11-2012', 'dd-mm-yyyy'), 'NEW');
insert into TEMP1 (RDATA, RUSER)
values (to_date('05-11-2012', 'dd-mm-yyyy'), 'NEW');
insert into TEMP1 (RDATA, RUSER)
values (to_date('05-11-2012', 'dd-mm-yyyy'), 'NEW');
insert into TEMP1 (RDATA, RUSER)
values (to_date('05-11-2012', 'dd-mm-yyyy'), 'NEW');
insert into TEMP1 (RDATA, RUSER)
values (to_date('05-11-2012', 'dd-mm-yyyy'), 'NEW');
insert into TEMP1 (RDATA, RUSER)
values (to_date('05-11-2012', 'dd-mm-yyyy'), 'NEW');
commit;
insert into TEMP2 (RDATA, RUSER)
values (to_date('01-11-2012', 'dd-mm-yyyy'), 'OLD');
insert into TEMP2 (RDATA, RUSER)
values (to_date('01-11-2012', 'dd-mm-yyyy'), 'OLD');
insert into TEMP2 (RDATA, RUSER)
values (to_date('01-11-2012', 'dd-mm-yyyy'), 'OLD');
insert into TEMP2 (RDATA, RUSER)
values (to_date('01-11-2012', 'dd-mm-yyyy'), 'OLD');
insert into TEMP2 (RDATA, RUSER)
values (to_date('01-11-2012', 'dd-mm-yyyy'), 'OLD');
insert into TEMP2 (RDATA, RUSER)
values (to_date('02-11-2012', 'dd-mm-yyyy'), 'OLD');
insert into TEMP2 (RDATA, RUSER)
values (to_date('02-11-2012', 'dd-mm-yyyy'), 'OLD');
insert into TEMP2 (RDATA, RUSER)
values (to_date('02-11-2012', 'dd-mm-yyyy'), 'OLD');
commit;
SQL> select new,a.rdata,b.rdata from
2 (select rdata as new,count(*)rdata from temp1 group by rdata ) a,
3 (select rdata as old,count(*)rdata from temp2 group by rdata ) b
4 where a.new=b.old(+)
5 ;
NEW RDATA RDATA
----------- ---------- ----------
2012-11-1 1 5
2012-11-2 3 3
2012-11-5 8