快捷導(dǎo)航

每個(gè)分類取最新的幾條的SQL實(shí)現(xiàn)代碼

更新時(shí)間：2012年08月02日 16:25:33 作者：

每個(gè)分類取最新的幾條的SQL實(shí)現(xiàn)代碼，需要的朋友可以參考下

CREATE TABLE table1( [ID] [bigint] IDENTITY(1,1) NOT NULL, [Name] [nvarchar](128) NOT NULL, [class] int not null, [date] datetime not null)class 表示分類編號(hào)。分類數(shù)不固定，至少有上千種分類
date 表示該條記錄被更新的時(shí)間
我們現(xiàn)在想獲得每個(gè)分類最新被更新的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('杭州大明有限公司','汽車(chē)1','2010-8-1')
insert into #temp(company,product,inputDate) values('杭州大明有限公司','汽車(chē)2','2010-8-1')
insert into #temp(company,product,inputDate) values('杭州大明有限公司','汽車(chē)3','2010-8-1')
insert into #temp(company,product,inputDate) values('杭州大明有限公司','汽車(chē)4','2010-8-1')
insert into #temp(company,product,inputDate) values('杭州大明有限公司','汽車(chē)5','2010-7-1')
insert into #temp(company,product,inputDate) values('北京小科有限公司','汽車(chē)1','2010-8-1')
insert into #temp(company,product,inputDate) values('北京小科有限公司','汽車(chē)2','2010-8-1')
insert into #temp(company,product,inputDate) values('北京小科有限公司','汽車(chē)3','2010-8-1')
insert into #temp(company,product,inputDate) values('北京小科有限公司','汽車(chē)4','2010-8-1')
insert into #temp(company,product,inputDate) values('上海有得有限公司','汽車(chē)1','2010-8-1')
insert into #temp(company,product,inputDate) values('上海有得有限公司','汽車(chē)2','2010-8-1')
insert into #temp(company,product,inputDate) values('上海有得有限公司','汽車(chē)3','2010-8-1')
insert into #temp(company,product,inputDate) values('上海有得有限公司','汽車(chē)4','2010-8-1')
insert into #temp(company,product,inputDate) values('天津旺旺有限公司','汽車(chē)4','2010-8-1')
insert into #temp(company,product,inputDate) values('天津旺旺有限公司','汽車(chē)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 序號(hào),a.company,a.product,a.inputDate
from #temp a
) b
where 序號(hào)>=@num
order by 序號(hào),inputDate desc
end
go
getdata 2
/*
結(jié)果
1 杭州大明有限公司汽車(chē)1 2010-08-01 00:00:00.000
1 北京小科有限公司汽車(chē)1 2010-08-01 00:00:00.000
1 上海有得有限公司汽車(chē)1 2010-08-01 00:00:00.000
1 天津旺旺有限公司汽車(chē)4 2010-08-01 00:00:00.000
2 天津旺旺有限公司汽車(chē)5 2010-08-01 00:00:00.000
2 上海有得有限公司汽車(chē)2 2010-08-01 00:00:00.000
2 北京小科有限公司汽車(chē)2 2010-08-01 00:00:00.000
2 杭州大明有限公司汽車(chē)2 2010-08-01 00:00:00.000
3 杭州大明有限公司汽車(chē)3 2010-08-01 00:00:00.000
3 北京小科有限公司汽車(chē)3 2010-08-01 00:00:00.000
3 上海有得有限公司汽車(chē)3 2010-08-01 00:00:00.000
4 北京小科有限公司汽車(chē)4 2010-08-01 00:00:00.000
4 北京小科有限公司汽車(chē)4 2010-08-01 00:00:00.000
4 上海有得有限公司汽車(chē)4 2010-08-01 00:00:00.000
4 杭州大明有限公司汽車(chē)4 2010-08-01 00:00:00.000
5 杭州大明有限公司汽車(chē)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 序號(hào),a.company,a.product,a.inputDate
from #temp a
) b
where 序號(hào)>=@num
order by 序號(hào),inputDate desc
end
getdata2005 4
select * from #temp
select ( select count(*) from #temp where company+ product<=a.company+a.product) as 序號(hào),a.company,a.product,a.inputDate
,a.company+a.product as 唯一標(biāo)志一行
from #temp a
order by company,product

復(fù)制代碼代碼如下:

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