一种典型问题的解决－取一个小时内的最后两条记录

取一个小时内的最后两条记录。

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)=0  and  B>A.B) <2

drop table tab1

结果集：

程序代码

(8 行受影响)
A    B
—- ———————–
A    2007-08-09 10:12:00.000
A    2007-08-09 10:13:00.000
A    2007-08-09 11:42:00.000
A    2007-08-09 11:29:00.000

(4 行受影响)