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