sqlserver巧用row_number和partition by分組取top資料

標籤：des   style   blog   http   color   io   strong   資料   

From : http://www.jb51.net/article/29162.htm

--1.建立測試表 create table #score ( name varchar(20), subject varchar(20), score int ) --2.插入測試資料 insert into #score(name,subject,score) values(‘張三‘,‘語文‘,98) insert into #score(name,subject,score) values(‘張三‘,‘數學‘,80) insert into #score(name,subject,score) values(‘張三‘,‘英語‘,90) insert into #score(name,subject,score) values(‘李四‘,‘語文‘,88) insert into #score(name,subject,score) values(‘李四‘,‘數學‘,86) insert into #score(name,subject,score) values(‘李四‘,‘英語‘,88) insert into #score(name,subject,score) values(‘李明‘,‘語文‘,60) insert into #score(name,subject,score) values(‘李明‘,‘數學‘,86) insert into #score(name,subject,score) values(‘李明‘,‘英語‘,88) insert into #score(name,subject,score) values(‘林風‘,‘語文‘,74) insert into #score(name,subject,score) values(‘林風‘,‘數學‘,99) insert into #score(name,subject,score) values(‘林風‘,‘英語‘,59) insert into #score(name,subject,score) values(‘嚴明‘,‘英語‘,96) --3.取每個學科的前3名資料 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  

 文法形式：ROW_NUMBER() OVER(PARTITION BY COL1 ORDER BY COL2) 
解釋：根據COL1分組，在分組內部根據 COL2排序，而此Function Compute的值就表示每組內部排序後的順序編號（組內連續的唯一的) 結果：    --------------------------->>>