SQL排序
今天经理出了题SQL语句:每个学生按照语文成绩的排名,如果没有成绩那就排名最后,要求有列 排名名次,学生姓名,成绩
2张表分别:
![SQL排序 - 小蛇 - 小蛇--](http://img.ph.126.net/2mg26jRuxcwIjC7yD4Ek8w==/3230488307709932151.jpg)
![SQL排序 - 小蛇 - 小蛇--](http://img609.ph.126.net/kWSio-56rAwSp2qNACU3-g==/1893200693357643416.bmp)
rank () over:
select rank () over (order by a.Score desc ) as 排名名次,a.SName as 学生姓名,a.Score as 成绩 from
(select TScore.Score as score,TStudent.SName from TStudent left join TScore on TScore.Sno =TStudent.SNo
where isnull(TScore.CourseID,'A02')='A02') as a order by a.Score desc
如果成绩为空的话序号就都显示一样
row_number() over :
select row_number() over (order by a.Score desc ) as 排名名次,a.SName as 学生姓名,a.Score as 成绩 from
(select TScore.Score as score,TStudent.SName from TStudent left join TScore on TScore.Sno =TStudent.SNo
where isnull(TScore.CourseID,'A02')='A02') as a order by a.Score desc
row_number() over 按照行来排序
dense_rank () over:
select dense_rank () over(order by a.Score desc ) as 排名名次,a.SName as 学生姓名,a.Score as 成绩 from
(select TScore.Score as score,TStudent.SName from TStudent left join TScore on TScore.Sno =TStudent.SNo
where isnull(TScore.CourseID,'A02')='A02') as a order by a.Score desc
如果成绩是一样的话,序号也显示一样,好比说John的成绩是77,那他的排名也是3,而Marry的排名是4