每个分类取最新的几条的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′,’二零一零-8-1’卡塔尔(قطر‎ insert into
#temp(company,product,inputDate卡塔尔国values(‘阿德莱德大明有限公司’,’汽车2′,’2008-8-1’卡塔尔国 insert into
#temp(company,product,inputDateState of Qatarvalues(‘圣何塞大明有限公司’,’小车3′,’二零一零-8-1’State of Qatar insert into
#temp(company,product,inputDate卡塔尔(قطر‎values(‘南京大明有限公司’,’小车4′,’二零一零-8-1’State of Qatar insert into
#temp(company,product,inputDate卡塔尔国values(‘阿德莱德大明有限公司’,’小车5′,’二零零六-7-1’卡塔尔国 insert into
#temp(company,product,inputDate卡塔尔values(‘巴黎小科有限公司’,’汽车1′,’2009-8-1’State of Qatar insert into
#temp(company,product,inputDate卡塔尔国values(‘东方之珠小科有限公司’,’小车2′,’二零一零-8-1’State of Qatar insert into
#temp(company,product,inputDate)values(‘东京小科有限公司’,’小车3′,’二零一零-8-1’State of Qatar insert into
#temp(company,product,inputDate卡塔尔(قطر‎values(‘东京(Tokyo卡塔尔小科有限集团’,’汽车4′,’贰零壹零-8-1’卡塔尔(قطر‎ insert into
#temp(company,product,inputDate卡塔尔国values(‘北京有得有限公司’,’小车1′,’2009-8-1’卡塔尔(قطر‎ insert into
#temp(company,product,inputDate卡塔尔国values(‘北京有得有限集团’,’小车2′,’二零一零-8-1’卡塔尔 insert into
#temp(company,product,inputDate卡塔尔values(‘Hong Kong有得有限公司’,’小车3′,’2009-8-1’卡塔尔(قطر‎ insert into
#temp(company,product,inputDateState of Qatarvalues(‘东京有得有限公司’,’小车4′,’二零一零-8-1’卡塔尔(قطر‎ insert into
#temp(company,product,inputDate卡塔尔国values(‘圣Jose旺旺有限公司’,’小车4′,’二零零六-8-1’卡塔尔 insert into
#temp(company,product,inputDate卡塔尔(قطر‎values(‘蒙Trey旺旺有限公司’,’小车5′,’二零一零-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 贰零零玖-08-01 00:00:00.000 1 香水之都有得有限公司 小车1 二零零六-08-01
00:00:00.000 1 西雅图旺旺有限公司 小车4 二零零六-08-01 00:00:00.000 2
成都旺旺有限公司 小车5 二〇一〇-08-01 00:00:00.000 2 香港(Hong Kong卡塔尔(قطر‎有得有限公司 小车2
二零零六-08-01 00:00:00.000 2 香江小科有限公司 小车2 2008-08-01 00:00:00.000
2 德班大明有限公司 汽车2 二〇〇八-08-01 00:00:00.000 3 卢布尔雅那大明有限公司小车3 二零零六-08-01 00:00:00.000 3 香水之都小科有限公司 小车3 二零一零-08-01
00:00:00.000 3 北京有得有限公司 小车3 2008-08-01 00:00:00.000 4
香港小科有限公司 小车4 二零一零-08-01 00:00:00.000 4 北京小科有限公司 小车4
2010-08-01 00:00:00.000 4 东京有得有限企业 小车4 二零零六-08-01 00:00:00.000
4 圣Peter堡大明有限公司 小车4 二零一零-08-01 00:00:00.000 5 马那瓜大明有限集团小车5 二〇〇八-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.productState of Qatar as
序号,a.company,a.product,a.inputDate ,a.company+a.product as
唯生龙活虎标记生龙活虎行 from #temp a order by company,product 复制代码 代码如下: Code highlighting produced
by Actipro CodeHighlighter (freeware卡塔尔(قطر‎ 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 –第几页 as begin select companyname,product from (
select row_number() over (partition by companyname order by id) as
序号,* from #t ) a where 序号=@num1 order by companyname end go
getdata 4 go DROP procedure getdata

发表评论

电子邮件地址不会被公开。 必填项已用*标注