SQL Server分组查询最近的几笔记录示例

--1.查出所有符合件的信息

select b.MatlID,b.MatlDesc,b.SpecExp,b.UomBase,b.Price,a.TaxDesc,Max(a.ActDate) 'MaxActDate',a.VendorID,a.FullName

into #temp1 www.2cto.com

from 表1 a,表2 b where a.ActNo=b.ActNo and a.ActDate>='2012-01-01' and b.MatlID like 'M%'

group by b.MatlID,b.MatlDesc,b.SpecExp,b.UomBase,b.Price,a.TaxDesc,a.ActDate,a.VendorID,a.FullName

--2. 表#temp1的到#temp2

select * into #temp2 from #temp1 order by MatlID

delete from #temp2

--3. 料信息存入表#temp3

select MatlID into #temp3 from #temp1 group by MatlID order by MatlID

--4. 建游，取最近三次收料

declare @MatlID char(10)

declare CURSOR_TMP20120620 cursor for

select MatlID from #temp3

open CURSOR_TMP20120620

while @@fetch_status=0 --如果上一次操作成功循

begin www.2cto.com

fetch next from CURSOR_TMP20120620 into @MatlID --提取游中的料信息存入量

insert into #temp2

select top 3 * from #temp1 where MatlID=@MatlID order by MaxActDate desc -- 最近三次收料信息插入到表#temp2

end

close CURSOR_TMP20120620

deallocate CURSOR_TMP20120620

--Result:

select * from #temp2

作者 hangwei