partition by 语法

查询成绩表每科前3名：

select  *  from(

        select 学生ID,科目,score,ROW_NUMBER() over (partition by 科目 order by score) newcolumn from dbo.成绩表

) t where newcolumn<=3

查询学生表每个姓氏多少人并倒序

select left(stuname,1) 姓氏,COUNT(newcolumn) 人数 from(

        select *,ROW_NUMBER() over (partition by left(stuname,1) order by stuname) newcolumn from Student

) t group by left(stuname,1) order by COUNT(newcolumn) desc