取一个小时内的最后两条记录。
create table tab1(A varchar(2),B datetime)
insert tab1
select ‘A’,'2007-08-09 10:10:00′ union all
select ‘A’,'2007-08-09 10:11:00′ union all
select ‘A’,'2007-08-09 10:12:00′ union all
select ‘A’,'2007-08-09 10:13:00′ union all
select ‘A’,'2007-08-09 11:10:00′ union all
select ‘A’,'2007-08-09 11:22:00′ union all
select ‘A’,'2007-08-09 11:42:00′ union all
select ‘A’,'2007-08-09 11:29:00′
select *
from tab1 A
where (select count(*) from tab1 where datediff(hour,B,A.B)=0andB>A.B) <2
insert tab1
select ‘A’,'2007-08-09 10:10:00′ union all
select ‘A’,'2007-08-09 10:11:00′ union all
select ‘A’,'2007-08-09 10:12:00′ union all
select ‘A’,'2007-08-09 10:13:00′ union all
select ‘A’,'2007-08-09 11:10:00′ union all
select ‘A’,'2007-08-09 11:22:00′ union all
select ‘A’,'2007-08-09 11:42:00′ union all
select ‘A’,'2007-08-09 11:29:00′
select *
from tab1 A
where (select count(*) from tab1 where datediff(hour,B,A.B)=0andB>A.B) <2
drop table tab1
结果集:

(8 行受影响)
AB
—- ———————–
A2007-08-09 10:12:00.000
A2007-08-09 10:13:00.000
A2007-08-09 11:42:00.000
A2007-08-09 11:29:00.000
AB
—- ———————–
A2007-08-09 10:12:00.000
A2007-08-09 10:13:00.000
A2007-08-09 11:42:00.000
A2007-08-09 11:29:00.000
(4 行受影响)