求最后的5条记录,该怎么解决

求最后的5条记录
表格如下:
id time num 
01 9:01  
01 9:02
01 9:04
01 9:06
01 9:10
01 9:12
.. ...
02 8:33
02 9:02
02 9:04
02 9:06
02 9:10
02 9:12
... ...
03 ...

04 ...
..

05 ....
...

希望得到如下结果:
id time num
01 9:01 
01 9:02
01 9:04 
02 8:33
02 9:02
02 9:04 
... ... ....

每个ID 最小(或最大)时间的3条记录.

[解决办法]
select * from tb a
where 3>(select count(*) from tb b where b.id=a.id and b.time<a.time)

select * from tb a where time in (select top 3 time from tb b where b.id=a.id)

以上是两种不同的写法.当然写法还有很多.

如果同id的time值有相同,这种做法会有问题,可以借助临表的identity列实现.
[解决办法]

SQL code

--最小时间select * from tablename ta where time in (select top 3 time from tablename where tablename.id=ta.id order by time) --最大时间select * from tablename ta where time in (select top 3 time from tablename where tablename.id=ta.id order by time desc)