1、PARTITION BY 分區函數

PARTITION BY 分區函數  可以返回一個組別中多條記錄

GROUP BY      分組函數   一般只有一條反映統計值的結果

2. 使用語法

row_number() over(partition by ... order by ...)
rank() over(partition by ... order by ...)
dense_rank() over(partition by ... order by ...)
count() over(partition by ... order by ...)
max() over(partition by ... order by ...)
min() over(partition by ... order by ...)
sum() over(partition by ... order by ...)
avg() over(partition by ... order by ...)
first_value() over(partition by ... order by ...)
last_value() over(partition by ... order by ...)
lag() over(partition by ... order by ...)
lead() over(partition by ... order by ...)

3.使用案例

求每個班級取成績最高的一名同學的id，分數，班級

SELECT ROW_NUMBER() OVER (PARTITION BY class_id ORDER BY degree desc), id,class_id,degree
     FROM myTable
where RowNum = 1;