快捷導航

sqlserver巧用row_number和partition by分組取top數(shù)據(jù)

更新時間：2011年12月20日 11:55:07 作者：

SQL Server 2005后之后，引入了row_number()函數(shù)，row_number()函數(shù)的分組排序功能使這種操作變得非常簡單

分組取TOP數(shù)據(jù)是T-SQL中的常用查詢，如學生信息管理系統(tǒng)中取出每個學科前3名的學生。這種查詢在SQL Server 2005之前，寫起來很繁瑣，需要用到臨時表關(guān)聯(lián)查詢才能取到。SQL Server 2005后之后，引入了row_number()函數(shù)，row_number()函數(shù)的分組排序功能使這種操作變得非常簡單。下面是一個簡單示例：

復(fù)制代碼代碼如下:

 
--1.創(chuàng)建測試表 
create table #score 
( 
name varchar(20), 
subject varchar(20), 
score int 
) 
--2.插入測試數(shù)據(jù) 
insert into #score(name,subject,score) values('張三','語文',98) 
insert into #score(name,subject,score) values('張三','數(shù)學',80) 
insert into #score(name,subject,score) values('張三','英語',90) 
insert into #score(name,subject,score) values('李四','語文',88) 
insert into #score(name,subject,score) values('李四','數(shù)學',86) 
insert into #score(name,subject,score) values('李四','英語',88) 
insert into #score(name,subject,score) values('李明','語文',60) 
insert into #score(name,subject,score) values('李明','數(shù)學',86) 
insert into #score(name,subject,score) values('李明','英語',88) 
insert into #score(name,subject,score) values('林風','語文',74) 
insert into #score(name,subject,score) values('林風','數(shù)學',99) 
insert into #score(name,subject,score) values('林風','英語',59) 
insert into #score(name,subject,score) values('嚴明','英語',96) 
--3.取每個學科的前3名數(shù)據(jù) 
select * from 
( 
select subject,name,score,ROW_NUMBER() over(PARTITION by subject order by score desc) as num from #score 
) T where T.num <= 3 order by subject 
--4.刪除臨時表 
truncate table #score 
drop table #score 