sqlserver巧用row_numberとpartition byグループはtopデータを取ります。


グループトップデータはT-SQLでよく使われている照会です。学生情報管理システムで各学科の上位3名の学生を取り出します。このようなクエリはSQL Server 2005の前に、書き込みが煩雑で、臨時テーブル関連のクエリを使用して取得できます。SQL Serverは2005年以降、row_を導入しました。number()関数,row_number()関数のパケット順序付け機能はこの動作を非常に簡単にした。以下は簡単な例です。
 
--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(PATION BY COL 1 ORR BY COL 2)は、COL 1パケットの内部でCOL 2に従って並べ替えられ、この関数で計算された値は、各グループの内部並べ替え後の順序番号(グループ内で連続する唯一のもの)を表していると説明しています。