DROP TABLE test;

create table test (peer_id number,log_time date,action varchar2(20),cause varchar2(20));

insert into test VALUES (1,sysdate-4,'connect','连接');

insert into test VALUES (1,sysdate-9999,'connect','连接');

insert into test VALUES (1,sysdate-9991,'disconnect','退出');

insert into test VALUES (2,sysdate-4,'connect','连接');

insert into test VALUES (2,sysdate-3,'disconnect','退出');

insert into test VALUES (3,sysdate-4,'connect','连接');

insert into test VALUES (4,sysdate-4,'disconnect','退出');

commit;

select * from test;

SELECT peer_id

,max(action) keep(dense_rank last order by log_time)

,max(decode(action,'connect',log_time))

,max(decode(action,'disconnect',log_time))

FROM test

GROUP BY peer_id;

ITPub博客

ORACLE中的KEEP()使用方法