每個分類取最新的幾條的SQL實現(xiàn)代碼
更新時間：2012年08月02日 16:25:33 作者：
每個分類取最新的幾條的SQL實現(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 表示分類編號。 分類數(shù)不固定， 至少有上千種分類
date 表示該條記錄被更新的時間
我們現(xiàn)在想獲得每個分類最新被更新的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
/*
結(jié)果
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 唯一標(biāo)志一行
from #temp a
order by company,product
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 --第幾頁
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
date 表示該條記錄被更新的時間
我們現(xiàn)在想獲得每個分類最新被更新的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
/*
結(jié)果
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 唯一標(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 --第幾頁
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
相關(guān)文章
關(guān)于SQL數(shù)據(jù)庫 msdb.dbo.sp_send_dbmail 函數(shù)發(fā)送郵件的場景分析
這篇文章主要介紹了關(guān)于SQL數(shù)據(jù)庫 msdb.dbo.sp_send_dbmail 函數(shù)發(fā)送郵件的場景分析,需要的朋友可以參考下2018-10-10
SQL行號排序和分頁(SQL查詢中插入行號 自定義分頁的另類實現(xiàn))
如何在SQL中對行進(jìn)行動態(tài)編號,加行號這個問題,在數(shù)據(jù)庫查詢中,是經(jīng)典的問題2012-07-07
Sql Server 查詢性能優(yōu)化之走出索引的誤區(qū)分析
很多朋友可能都正在犯下面所說的性能優(yōu)化誤區(qū)了，有需要的朋友可以參考一下Sql Server查詢性能優(yōu)化之走出索引的誤區(qū)2012-05-05
這篇文章主要介紹了SQL SERVER提交事務(wù)回滾機制,本篇文章通過簡要的案例,講解了該項技術(shù)的了解與使用,以下就是詳細(xì)內(nèi)容,需要的朋友可以參考下2021-07-07
SQL Server中有幾個可以讓你檢測、調(diào)整和優(yōu)化SQL Server性能的工具2012-08-08
SQL語句實例說明 方便學(xué)習(xí)mysql的朋友
我是在MySQL數(shù)據(jù)庫中做的測試,不同的數(shù)據(jù)庫有一定的差別方便學(xué)習(xí)mysql的朋友2012-09-09
distinct這個關(guān)鍵字來過濾掉多余的重復(fù)記錄只保留一條，但往往只用它來返回不重復(fù)記錄的條數(shù)，而不是用它來返回不重記錄的所有值。其原因是distinct只能返回它的目標(biāo)字段，而無法返回其它字段，接下來通過本篇文章給大家分享SQL中distinct的用法，需要的朋友可以參考下2015-09-09
深入SQL SERVER合并相關(guān)操作Union,Except,Intersect的詳解
本篇文章是對SQL SERVER合并相關(guān)操作Union,Except,Intersect進(jìn)行了詳細(xì)的分析介紹，需要的朋友參考下2013-06-06
SQL Server數(shù)據(jù)表字段自定義自增數(shù)據(jù)格式的方法
這篇文章主要介紹了SQL Server數(shù)據(jù)表字段自定義自增數(shù)據(jù)格式的方法,結(jié)合實例形式分析了SQL Server自增數(shù)據(jù)格式的定義方法與具體實現(xiàn)步驟,需要的朋友可以參考下2016-08-08
SQL 提權(quán) 常用命令，大家可以考慮下將sqlserver的服務(wù)運行權(quán)限設(shè)置為普通用戶，即可防止下面的提權(quán)。2009-07-07