提供了编程的基础技术教程

zl程序教程

您现在的位置是：首页 >  数据库

当前栏目

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

SQL代码 实现 最新 分类 每个 几条
2023-06-13 09:14:35 时间
CREATETABLEtable1([ID][bigint]IDENTITY(1,1)NOTNULL,[Name][nvarchar](128)NOTNULL,[class]intnotnull,[date]datetimenotnull)class表示分类编号。分类数不固定，至少有上千种分类
date表示该条记录被更新的时间
我们现在想获得每个分类最新被更新的5条记录。
解决方案
selectid,name,class,datefrom(selectid,name,class,date,row_number()over(partitionbyclassorderbydatedesc)asrowindexfromtable1)awhererowindex<=5
createtable#temp
(
companyvarchar(50),
productvarchar(50),
inputDatedatetime
)
insertinto#temp(company,product,inputDate)values("杭州大明有限公司","汽车1","2010-8-1")
insertinto#temp(company,product,inputDate)values("杭州大明有限公司","汽车2","2010-8-1")
insertinto#temp(company,product,inputDate)values("杭州大明有限公司","汽车3","2010-8-1")
insertinto#temp(company,product,inputDate)values("杭州大明有限公司","汽车4","2010-8-1")
insertinto#temp(company,product,inputDate)values("杭州大明有限公司","汽车5","2010-7-1")
insertinto#temp(company,product,inputDate)values("北京小科有限公司","汽车1","2010-8-1")
insertinto#temp(company,product,inputDate)values("北京小科有限公司","汽车2","2010-8-1")
insertinto#temp(company,product,inputDate)values("北京小科有限公司","汽车3","2010-8-1")
insertinto#temp(company,product,inputDate)values("北京小科有限公司","汽车4","2010-8-1")
insertinto#temp(company,product,inputDate)values("上海有得有限公司","汽车1","2010-8-1")
insertinto#temp(company,product,inputDate)values("上海有得有限公司","汽车2","2010-8-1")
insertinto#temp(company,product,inputDate)values("上海有得有限公司","汽车3","2010-8-1")
insertinto#temp(company,product,inputDate)values("上海有得有限公司","汽车4","2010-8-1")
insertinto#temp(company,product,inputDate)values("天津旺旺有限公司","汽车4","2010-8-1")
insertinto#temp(company,product,inputDate)values("天津旺旺有限公司","汽车5","2010-8-1")
select*from#temp
createprocgetdata
@numint
as
begin
selecttop4*from
(
select(selectcount(*)from#tempwherecompany=a.companyandproduct<=a.product)as序号,a.company,a.product,a.inputDate
from#tempa
)b
where序号>=@num
orderby序号,inputDatedesc
end
go
getdata2
/*
结果
1杭州大明有限公司汽车12010-08-0100:00:00.000
1北京小科有限公司汽车12010-08-0100:00:00.000
1上海有得有限公司汽车12010-08-0100:00:00.000
1天津旺旺有限公司汽车42010-08-0100:00:00.000
2天津旺旺有限公司汽车52010-08-0100:00:00.000
2上海有得有限公司汽车22010-08-0100:00:00.000
2北京小科有限公司汽车22010-08-0100:00:00.000
2杭州大明有限公司汽车22010-08-0100:00:00.000
3杭州大明有限公司汽车32010-08-0100:00:00.000
3北京小科有限公司汽车32010-08-0100:00:00.000
3上海有得有限公司汽车32010-08-0100:00:00.000
4北京小科有限公司汽车42010-08-0100:00:00.000
4北京小科有限公司汽车42010-08-0100:00:00.000
4上海有得有限公司汽车42010-08-0100:00:00.000
4杭州大明有限公司汽车42010-08-0100:00:00.000
5杭州大明有限公司汽车52010-07-0100:00:00.000
*/
--sql2005
createprocgetdata2005
@numint
as
begin
selecttop4*from
(
selectrow_number()over(partitionbycompanyorderbyproduct)as序号,a.company,a.product,a.inputDate
from#tempa
)b
where序号>=@num
orderby序号,inputDatedesc
end
getdata20054
select*from#temp
select(selectcount(*)from#tempwherecompany+product<=a.company+a.product)as序号,a.company,a.product,a.inputDate
,a.company+a.productas唯一标志一行
from#tempa
orderbycompany,product
复制代码代码如下:

CodehighlightingproducedbyActiproCodeHighlighter(freeware)http://www.CodeHighlighter.com/-->ifobject_id(N"company")isnotnull
droptablecompany
go
createtablecompany
(
companynamevarchar(2),
productvarchar(60)
)
--公司1
insertintocompany
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
insertintocompany
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
insertintocompany
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
insertintocompany
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
insertintocompany
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
insertintocompany
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
insertintocompany
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
insertintocompany
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
insertintocompany
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
insertintocompany
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(selectObject_id("Tempdb..#t"))ISNULL
selectidentity(int,1,1)asid,*into#tfromcompany
orderbyleft(product,1),cast(substring(product,2,2)asint)
ifobject_id(N"getdata","P")isnotnull
droptablegetdata
go
createprocgetdata
@num1int--第几页
as
begin
selectcompanyname,productfrom
(
selectrow_number()over(partitionbycompanynameorderbyid)as序号,*
from#t
)a
where序号=@num1
orderbycompanyname
end
go
getdata4
go
DROPproceduregetdata

相关文章