3,491
社区成员
发帖
与我相关
我的任务
分享
--借下测试数据
create table ta(id int,name varchar2(20),tm date,num int);
insert into ta values(1 , 'aa',to_date('2009-10-1 11:20:15','yyyy-mm-dd hh24:mi:ss'), 1000);
insert into ta values(2 , 'bb',to_date('2009-10-1 10:10:15','yyyy-mm-dd hh24:mi:ss'),60);
insert into ta values(3 , 'aa',to_date('2009-10-1 10:01:15','yyyy-mm-dd hh24:mi:ss'),800);
insert into ta values(4 , 'bb',to_date('2009-10-1 9:10:15','yyyy-mm-dd hh24:mi:ss'),40 );
insert into ta values(5 , 'cc',to_date('2009-10-1 9:20:15','yyyy-mm-dd hh24:mi:ss'),500);
insert into ta values(6 , 'cc',to_date('2009-10-1 7:20:15','yyyy-mm-dd hh24:mi:ss'),400 );
insert into ta values(7 , 'aa',to_date('2009-11-1 11:20:15','yyyy-mm-dd hh24:mi:ss'),1000 );
insert into ta values(8 , 'bb',to_date('2009-11-1 10:10:15','yyyy-mm-dd hh24:mi:ss'),60);
insert into ta values(9 , 'aa',to_date('2009-11-1 10:01:15','yyyy-mm-dd hh24:mi:ss'),800);
insert into ta values(10, 'bb',to_date('2009-11-1 9:10:15','yyyy-mm-dd hh24:mi:ss'),40);
insert into ta values(11, 'cc',to_date('2009-11-1 9:20:15','yyyy-mm-dd hh24:mi:ss'),500);
insert into ta values(12, 'cc',to_date('2009-11-1 7:20:15','yyyy-mm-dd hh24:mi:ss'),400);
select * from ta a
where not exists(select 1 from ta b where a.name=b.name
and to_char(a.tm,'YYYY-MM-DD')=to_char(b.tm,'YYYY-MM-DD') and a.tm<b.tm)