每个分类取最新的几条的SQL实现代码

每个分类取最新的几条的SQL实现代码，需要的朋友可以参考下

CREATE TABLE table1( [ID] [bigint] IDENTITY(1,1) NOT NULL, [Name] [nvarchar](128) NOT NULL, [class] int not null, [date] datetime not null)class 表示分类编号。 分类数不固定， 至少有上千种分类
date 表示该条记录被更新的时间
我们现在想获得每个分类最新被更新的5条记录。
解决方案
select id,name,class,date from(select id,name,class,date ,row_number() over(partition by class order by date desc)as rowindex from table1) awhere rowindex <= 5
create table #temp
(
company varchar(50),
product varchar(50),
inputDate datetime
)
insert into #temp(company,product,inputDate) values('杭州大明有限公司','汽车1','2010-8-1')
insert into #temp(company,product,inputDate) values('杭州大明有限公司','汽车2','2010-8-1')
insert into #temp(company,product,inputDate) values('杭州大明有限公司','汽车3','2010-8-1')
insert into #temp(company,product,inputDate) values('杭州大明有限公司','汽车4','2010-8-1')
insert into #temp(company,product,inputDate) values('杭州大明有限公司','汽车5','2010-7-1')
insert into #temp(company,product,inputDate) values('北京小科有限公司','汽车1','2010-8-1')
insert into #temp(company,product,inputDate) values('北京小科有限公司','汽车2','2010-8-1')
insert into #temp(company,product,inputDate) values('北京小科有限公司','汽车3','2010-8-1')
insert into #temp(company,product,inputDate) values('北京小科有限公司','汽车4','2010-8-1')
insert into #temp(company,product,inputDate) values('上海有得有限公司','汽车1','2010-8-1')
insert into #temp(company,product,inputDate) values('上海有得有限公司','汽车2','2010-8-1')
insert into #temp(company,product,inputDate) values('上海有得有限公司','汽车3','2010-8-1')
insert into #temp(company,product,inputDate) values('上海有得有限公司','汽车4','2010-8-1')
insert into #temp(company,product,inputDate) values('天津旺旺有限公司','汽车4','2010-8-1')
insert into #temp(company,product,inputDate) values('天津旺旺有限公司','汽车5','2010-8-1')
select * from #temp
create proc getdata
@num int
as
begin
select top 4 * from
(
select ( select count(*) from #temp where company=a.company and product<=a.product) as 序号,a.company,a.product,a.inputDate
from #temp a
) b
where 序号>=@num
order by 序号,inputDate desc
end
go
getdata 2
/*
结果
1 杭州大明有限公司 汽车1 2010-08-01 00:00:00.000
1 北京小科有限公司 汽车1 2010-08-01 00:00:00.000
1 上海有得有限公司 汽车1 2010-08-01 00:00:00.000
1 天津旺旺有限公司 汽车4 2010-08-01 00:00:00.000
2 天津旺旺有限公司 汽车5 2010-08-01 00:00:00.000
2 上海有得有限公司 汽车2 2010-08-01 00:00:00.000
2 北京小科有限公司 汽车2 2010-08-01 00:00:00.000
2 杭州大明有限公司 汽车2 2010-08-01 00:00:00.000
3 杭州大明有限公司 汽车3 2010-08-01 00:00:00.000
3 北京小科有限公司 汽车3 2010-08-01 00:00:00.000
3 上海有得有限公司 汽车3 2010-08-01 00:00:00.000
4 北京小科有限公司 汽车4 2010-08-01 00:00:00.000
4 北京小科有限公司 汽车4 2010-08-01 00:00:00.000
4 上海有得有限公司 汽车4 2010-08-01 00:00:00.000
4 杭州大明有限公司 汽车4 2010-08-01 00:00:00.000
5 杭州大明有限公司 汽车5 2010-07-01 00:00:00.000
*/
--sql2005
create proc getdata2005
@num int
as
begin
select top 4 * from
(
select row_number() over (partition by company order by product ) as 序号,a.company,a.product,a.inputDate
from #temp a
) b
where 序号>=@num
order by 序号,inputDate desc
end
getdata2005 4
select * from #temp
select ( select count(*) from #temp where company+ product<=a.company+a.product) as 序号,a.company,a.product,a.inputDate
,a.company+a.product as 唯一标志一行
from #temp a
order by company,product
代码如下:

<br>Code highlighting produced by Actipro CodeHighlighter (freeware)http://www.CodeHighlighter.com/-->if object_id(N'company') is not null <br>drop table company <br>go <br>create table company <br>( <br>companyname varchar(2), <br>product varchar(60) <br>) <br>--公司1 <br>insert into company <br>select 'A','A1' union <br>select 'A','A2' union <br>select 'A','A3' union <br>select 'A','A4' union <br>select 'A','A5' union <br>select 'A','A6' union <br>select 'A','A7' union <br>select 'A','A8' union <br>select 'A','A9' union <br>select 'A','A10' <br>--公司2 <br>insert into company <br>select 'B','B1' union <br>select 'B','B2' union <br>select 'B','B3' union <br>select 'B','B4' union <br>select 'B','B5' union <br>select 'B','B6' union <br>select 'B','B7' union <br>select 'B','B8' union <br>select 'B','B9' union <br>select 'B','B10' <br>--公司3 <br>insert into company <br>select 'C','C1' union <br>select 'C','C2' union <br>select 'C','C3' union <br>select 'C','C4' union <br>select 'C','C5' union <br>select 'C','C6' union <br>select 'C','C7' union <br>select 'C','C8' union <br>select 'C','C9' union <br>select 'C','C10' <br>--公司4 <br>insert into company <br>select 'D','D1' union <br>select 'D','D2' union <br>select 'D','D3' union <br>select 'D','D4' union <br>select 'D','D5' union <br>select 'D','D6' union <br>select 'D','D7' union <br>select 'D','D8' union <br>select 'D','D9' union <br>select 'D','D10' <br>--公司5 <br>insert into company <br>select 'E','E1' union <br>select 'E','E2' union <br>select 'E','E3' union <br>select 'E','E4' union <br>select 'E','E5' union <br>select 'E','E6' union <br>select 'E','E7' union <br>select 'E','E8' union <br>select 'E','E9' union <br>select 'E','E10' <br>--公司6 <br>insert into company <br>select 'F','F1' union <br>select 'F','F2' union <br>select 'F','F3' union <br>select 'F','F4' union <br>select 'F','F5' union <br>select 'F','F6' union <br>select 'F','F7' union <br>select 'F','F8' union <br>select 'F','F9' union <br>select 'F','F10' <br>--公司7 <br>insert into company <br>select 'G','G1' union <br>select 'G','G2' union <br>select 'G','G3' union <br>select 'G','G4' union <br>select 'G','G5' union <br>select 'G','G6' union <br>select 'G','G7' union <br>select 'G','G8' union <br>select 'G','G9' union <br>select 'G','G10' <br>--公司8 <br>insert into company <br>select 'H','H1' union <br>select 'H','H2' union <br>select 'H','H3' union <br>select 'H','H4' union <br>select 'H','H5' union <br>select 'H','H6' union <br>select 'H','H7' union <br>select 'H','H8' union <br>select 'H','H9' union <br>select 'H','H10' <br>--公司9 <br>insert into company <br>select 'I','I1' union <br>select 'I','I2' union <br>select 'I','I3' union <br>select 'I','I4' union <br>select 'I','I5' union <br>select 'I','I6' union <br>select 'I','I7' union <br>select 'I','I8' union <br>select 'I','I9' union <br>select 'I','I10' <br>--公司10 <br>insert into company <br>select 'J','J1' union <br>select 'J','J2' union <br>select 'J','J3' union <br>select 'J','J4' union <br>select 'J','J5' union <br>select 'J','J6' union <br>select 'J','J7' union <br>select 'J','J8' union <br>select 'J','J9' union <br>select 'J','J10' <br>IF (select Object_id('Tempdb..#t')) IS NULL <br>select identity(int,1,1) as id,* into #t from company <br>order by left(product,1),cast(substring(product,2,2) as int) <br>if object_id(N'getdata','P') is not null <br>drop table getdata <br>go <br>create proc getdata <br>@num1 int --第几页 <br>as <br>begin <br>select companyname,product from <br>( <br>select row_number() over (partition by companyname order by id) as 序号,* <br>from #t <br>) a <br>where 序号=@num1 <br>order by companyname <br>end <br>go <br>getdata 4 <br>go <br>DROP procedure getdata