表tempTable, 字段qty,fdate ,数据如下:
insert into tempTable(qty,fdate) values (5,to_date('2009-01-02','yyyy--mm-dd'));
insert into tempTable(qty,fdate) values (6,to_date('2009-01-03','yyyy--mm-dd'));
insert into tempTable(qty,fdate) values (7,to_date('2009-02-04','yyyy--mm-dd'));
insert into tempTable(qty,fdate) values (8,to_date('2009-02-05','yyyy--mm-dd'));
insert into tempTable(qty,fdate) values (9,to_date('2009-03-06','yyyy--mm-dd'));
insert into tempTable(qty,fdate) values (10,to_date('2009-03-07','yyyy--mm-dd'));
insert into tempTable(qty,fdate) values (11,to_date('2009-04-02','yyyy--mm-dd'));
insert into tempTable(qty,fdate) values (12,to_date('2009-04-05','yyyy--mm-dd'));
insert into tempTable(qty,fdate) values (12,to_date('2009-04-06','yyyy--mm-dd'));
select aa.aqty,to_char(max(b.fdate),'yyyymmdd') from
( select max(qty) aqty ,to_char(fdate,'yyyymm') adate from tempTable a group by to_char(fdate,'yyyymm') ) aa
left join tempTable b on aa.aqty = b.qty and aa.adate = to_char(b.fdate,'yyyymm') group by aa.aqty
结果:
6 20090103
8 20090205
10 20090307
12 20090406