select identity(int,1,1) as rowid,* into #1 from 表 order by 分類,時間
declare @ts int 要取的條數
select a.* from #1 a inner join
(select deptid,min(rowid) as rowid from #1 group by deptid)b
on a.分類=b.分類 and a.rowid-b.rowid between 0 and @ts-1
如果时间字段包括毫秒保证是唯一的话, lsxaa(小李铅笔刀) 的基本是正确的,应该也是最简单的
后面要<=前几条
如果时间字段只是日期的话这样就不对了,比如假设第一个日期是2005-02-03,后面两条日期都是
2005-02-02,条件是取两条,下面语句的结果是后两条一个都取不到,实际是应该取一条的
select *
from 表 t
where (select count(*) from 表 where 分类=t.分类 and 时间>=t.时间)<=前几条
select identity(int,1,1) as rowid,* into #1 from 表 order by 分類
declare @ts int 要取的條數
select a.* from #1 a inner join
(select * from #1)b
on a.分類=b.分類 and b.rowid-a.rowid between 0 and @ts-1