sql server 2005中的分割槽函式用法(partition by 欄位)

partition  by關鍵字是分析性函式的一部分，它和聚合函式不同的地方在於它能返回一個分組中的多條記錄，而聚合函式一般只有一條反映統計值的記錄，partition  by用於給結果集分組，如果沒有指定那麼它把整個結果集作為一個分組 

create database StudentDB
go

use StudentDB
go

create table Student  --學生成績表
(
 id int,  --主鍵
 Grade int, --班級
 Score int --分數
)
go

insert Student 
    select 1,1,88
union all select 2,1,66
union all select 3,1,75
union all select 4,2,30
union all select 5,2,70
union all select 6,2,80
union all select 7,2,60
union all select 8,3,90
union all select 9,3,70
union all select 10,3,80

go

--所有學生資訊
select * from Student

id          Grade       Score
----------- ----------- -----------
1           1           88
2           1           66
3           1           75
4           2           30
5           2           70
6           2           80
7           2           60
8           3           90
9           3           70
10          3           80

(10 行受影響)

--不分班按學生成績排名
select *,ROW_NUMBER() over(order by Score desc) as Sequence from Student

id          Grade       Score       Sequence
----------- ----------- ----------- --------------------
8           3           90          1
1           1           88          2
6           2           80          3
10          3           80          4
3           1           75          5
9           3           70          6
5           2           70          7
2           1           66          8
7           2           60          9
4           2           30          10

(10 行受影響)

--分班後按學生成績排名
select *,row_number() over(partition by Grade order by Score desc) as Sequence from Student

id          Grade       Score       Sequence
----------- ----------- ----------- --------------------
1           1           88          1
3           1           75          2
2           1           66          3
6           2           80          1
5           2           70          2
7           2           60          3
4           2           30          4
8           3           90          1
10          3           80          2
9           3           70          3

(10 行受影響)