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博客 ” ,链接:http://blog.itpub.net/23009281/viewspace-755740/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/23009281/viewspace-755740/