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