SQL Server 中row_number() over(order by )和rank、dense_rank()的区别

1.row_number()

select row_number() over (order by score desc) as rownumber,
       aa.examname,
       aa.studentid,
       aa.score
from
(     
select examname,studentid,score from examine
where examname='期末考试'
   and examid=221
   and studentid between 201200001 and 201200100
) aa

结果：

rownumber为2,3,4几个的成绩都是148分，排名却不同。

2.rank()

select rank()  over (order by score desc) as rownumber,
       aa.examname,
       aa.studentid,
       aa.score
from
(     
select examname,studentid,score from examine
where examname='期末考试'
   and examid=221
   and studentid between 201200001 and 201200100
) aa

结果：

相同的分数相同的排名，中间缺少了第3,4,5名

3.dense_rank()

select dense_rank()  over (order by score desc) as rownumber,
       aa.examname,
       aa.studentid,
       aa.score
from
(     
select examname,studentid,score from examine
where examname='期末考试'
   and examid=221
   and studentid between 201200001 and 201200100
) aa

结果：

这样是不是更好呢！

其实存在就有存在的理由，只是找到合适的场合。

 

这三个函数还可以加partition by的，类似这样：row_number()  over (partition by xx order by xx)

这里只贴上一点代码和查询的一些结果，因为各个表之间关系设计的太烂了，每个子查询要连接好几张表然后在N个union all,导致代码太多而且看上去非常繁琐冗杂，我都懒得再回头看。用到的时候，很自然过渡到partition by,上面的三点就是我从这个里面抽取出来的。

SELECT AAAA.*
FROM
(
SELECT AAA.*,
       ROW_NUMBER() OVER (PARTITION BY AAA.gradeid, AAA.gradecid, AAA.考试名称 order by AAA.总分 DESC) 名次
FROM
(
select AA.gradeid,
       AA.年级,
       AA.班级,
       AA.gradecid,
       AA.classid,
       AA.studentname 学生姓名,
       AA.考试名称,
       max(case AA.科目 when '总分' then aa.score end) 总分,
       max(case AA.科目 when '语数外总分' then aa.score end) 语数外总分,
       max(case AA.科目 when '语文' then aa.score end) 语文,

……

) AAA
) AAAA
WHERE AAAA.名次<=100
order by gradeid,gradecid,7,8 desc

得出的结果就是每个年级按总分排名前100名，然后上面理综、文综、语数外等等所有成绩都在里面。维度太多，包括考试名称，再分初中和高中，然后再分年级，再到班级。

这里的row_number()同样可以换成rank(),dense_rank().