SQL OVER() Partition By Order By

--Partition by 作用是分组，没有进行聚合,返回多条记录。 order by 是排序
--写法         函数名 +over（partition by）别名
--写法         函数名 +over（order by）别名
--写法         函数名 +over（partition by 列名 order by 列名）别名
Declare @tbl1 table(
name nvarchar(20),
course nvarchar(20),
score int
)
insert into @tbl1 values ( 'Alen' , 'math' , 98),( 'Alen' , 'chinese' , 95),( 'Alen' , 'english' , 76),( 'Tom' , 'math' , 85),
( 'Tom' , 'chinese' , 98),( 'Tom' , 'english' , 69),( 'Jack' , 'math' , 93),( 'Jack' , 'chinese' , 90),( 'Jack' , 'english' , 89)

---------------------------------------------------------------------------------------

--以名称分组，求总分
select name,course,score,
SUM(score) over(partition by name) ranking  
from @tbl1

-------------------------------------------------------------------------------------

--不分组对分数进行排名    出现两个相同最高分，则排名结果为1，1，3
select name,course,score,
rank() over(order by score desc) ranking
from @tbl1
--不分组对分数进行排名    出现两个相同最高分，则排名结果为1，1，2
select name,course,score,
dense_rank() over(order by score desc) ranking
from @tbl1
--ROW_NUMBER()只是计数，当分数相等时，会出现排名不等。出现两个相同最高分，则排名结果为1，2，3
select name,course,score,
ROW_NUMBER() over(order by score desc) ranking
from @tbl1

--------------------------------------------------------------------------------------

--以名称分组，按成绩从高到低排序
select name,course,score,
ROW_NUMBER() over(partition by name order by score desc) ranking  
from @tbl1
--以课程分组，按成绩从高到低排序
select name,course,score,
ROW_NUMBER() over(partition by course order by score desc) ranking
from @tbl1

partition by 注意事项：

当使用partition by做分组后的排名时，将排名的过滤条件放到最外层。将其他的查询条件放到里层。如果将查询条件都放到外层，有可能会造成查询结果为空。

--已课程分组，查询分数在90分以上的各科最高分。
select * from 
(select name,course,score,
ROW_NUMBER() over(partition by course order by score desc) ranking from @tbl1
where score>90--此查询条件需要在内层
) A 
WHERE A.ranking=1 --排名作为最外层的查询条件