每個分類取最新的幾條的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)文章
要對特定類別的列進(jìn)行一些操作，寫了以下的一段代碼，比較方便檢查數(shù)據(jù)庫內(nèi)同一類別的所有列，示例用來查所有nvarchar的列2013-02-02
SQL實現(xiàn)篩選出連續(xù)3天登錄用戶與窗口函數(shù)的示例代碼
本文主要介紹了SQL實現(xiàn)篩選出連續(xù)3天登錄用戶與窗口函數(shù)的示例代碼，文中通過示例代碼介紹的非常詳細(xì)，對大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價值，需要的朋友們下面隨著小編來一起學(xué)習(xí)學(xué)習(xí)吧2022-04-04
SQL Server數(shù)據(jù)庫中批量導(dǎo)入數(shù)據(jù)的四種方法總結(jié)
數(shù)據(jù)導(dǎo)入一直是項目人員比較頭疼的問題。其實，在SQL Server中集成了很多成批導(dǎo)入數(shù)據(jù)的方法，接下來為大家介紹下常用的四種批量導(dǎo)入數(shù)據(jù)的方法，感興趣的各位可以參考下哈2013-03-03
這篇文章介紹了sql語句like多個條件的寫法實例，有需要的朋友可以參考一下2013-10-10
這篇文章介紹了sql server查詢語句阻塞優(yōu)化性能的方法，對大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價值，需要的朋友們下面隨著小編來一起學(xué)習(xí)學(xué)習(xí)吧2022-04-04
SQLSERVER對索引的利用及非SARG運算符認(rèn)識
SQL對篩選條件簡稱：SARG（search argument/SARG）當(dāng)然這里不是說SQLSERVER的where子句，是說SQLSERVER對索引的利用,感興趣的朋友可以了解下，或許本文的知識點對你有所幫助哈2013-02-02
SQL Server 索引維護(hù)sql語句,有需要的朋友可以參考下。2009-08-08
這篇文章主要介紹了SQL多表聯(lián)合查詢時如何采用字段模糊匹配，具有很好的參考價值，希望對大家有所幫助。如有錯誤或未考慮完全的地方，望不吝賜教2022-11-11
自動備份mssql server數(shù)據(jù)庫并壓縮的批處理腳本
windows下，使用mssql命令行工具sqlcmd備份數(shù)據(jù)庫，并調(diào)用rar壓縮；不借助mssql"維護(hù)計劃"功能，拜托權(quán)限問題。2011-07-07
使用 SQL 服務(wù)器時,"評估期已過期"錯誤消息(解決方法)
這篇文章主要介紹了使用 SQL 服務(wù)器時，"評估期已過期"錯誤消息,本文分步驟給大家分享解決方法，需要的朋友可以參考下2019-12-12