数据如下:
REEMPLOYEEID SCANTIME
100000000088 2011-12-01 10:27:14
100000000088 2011-12-01 10:32:00
100000000088 2011-12-01 10:32:06
100000000104 2011-12-01 10:35:54
100000000088 2011-12-01 10:55:44
100000000106 2011-12-01 10:56:33 求结果:
REEMPLOYEEID SCANTIME 100000000088 2011-12-01 10:55:44
REEMPLOYEEID SCANTIME
100000000088 2011-12-01 10:27:14
100000000088 2011-12-01 10:32:00
100000000088 2011-12-01 10:32:06
100000000104 2011-12-01 10:35:54
100000000088 2011-12-01 10:55:44
100000000106 2011-12-01 10:56:33 求结果:
REEMPLOYEEID SCANTIME 100000000088 2011-12-01 10:55:44
from
(with a as(
select '100000000088' as reemployeeid, '2011-12-01 10:27:14' as scantime from dual
union all
select '100000000088','2011-12-01 10:32:00' from dual
union all
select '100000000088','2011-12-01 10:32:06' from dual
union all
select '100000000104','2011-12-01 10:35:54' from dual
union all
select '100000000088','2011-12-01 10:55:44' from dual
union all
select '100000000106','2011-12-01 10:56:33' from dual
)
select reemployeeid, scantime, row_number()
over (order by scantime desc) as ss from a) sub
where sub.ss = 2
select * from (select * from ss ORDER BY CANTIME desc )
where rownum<=2
minus
select * from (select * from ss ORDER BY CANTIME desc )
where rownum<=1
from (select t.reemployeeid,
t.scantime,
RANK() OVER(order by scantime) as RowNumber
from tab t) b
where b.RowNumber + 1 = (select max(RowNumber)
from (select t.reemployeeid,
t.scantime,
RANK() OVER(order by scantime) as RowNumber
from tab t))
(
ReemployeeID VARCHAR2(20),
ScanTime DATE
);INSERT INTO T26 VALUES('100000000088', to_date('2011-12-01 10:27:14', 'YYYY-MM-DD HH24:MI:SS'));
INSERT INTO T26 VALUES('100000000088', to_date('2011-12-01 10:32:00', 'YYYY-MM-DD HH24:MI:SS'));
INSERT INTO T26 VALUES('100000000088', to_date('2011-12-01 10:35:54', 'YYYY-MM-DD HH24:MI:SS'));
INSERT INTO T26 VALUES('100000000104', to_date('2011-12-01 10:32:06', 'YYYY-MM-DD HH24:MI:SS'));
INSERT INTO T26 VALUES('100000000088', to_date('2011-12-01 10:55:44', 'YYYY-MM-DD HH24:MI:SS'));
INSERT INTO T26 VALUES('100000000106', to_date('2011-12-01 10:56:33', 'YYYY-MM-DD HH24:MI:SS'));
结果
然后取n=2的记录
select '100000000088' as reemployeeid, '2011-12-01 10:27:14' as scantime from dual
union all
select '100000000088','2011-12-01 10:32:00' from dual
union all
select '100000000088','2011-12-01 10:32:06' from dual
union all
select '100000000104','2011-12-01 10:35:54' from dual
union all
select '100000000088','2011-12-01 10:55:44' from dual
union all
select '100000000106','2011-12-01 10:56:33' from dual
)
select * from (select * from a order by scantime desc) where rownum<=2
minus
select * from (select * from a ORDER BY scantime desc) where rownum<=1
over (order by scantime desc) as ss from a) sub
where sub.ss = 2