数据,创建表,查看数据表(代码)~
1、按成绩(不分科目)进行排序,并显示排名,Score 重复时保留名次空缺
select *,RANK()over(order by score desc)名次 from SC
2、按成绩(不分科目)进行排序,并显示排名,Score 重复时合并名次(并列第…)
select *,DENSE_RANK()over(order by score desc)名次 from SC
3、按各科成绩(分科目)进行排序,并显示排名,Score 重复时保留名次空缺
--ways 1
select * ,
(select count(score)+1 from
SC B where B.C# = A.C# and B.score > A.score) 名次 from
SC A order by C#,名次
--ways 2
select *,RANK()over(order by score desc)名次 from SC where C# ='01' union all
select *,RANK()over(order by score desc)名次 from SC where C# ='02' union all
select *,RANK()over(order by score desc)名次 from SC where C# ='03'
3、按各科成绩(分科目)进行排序,并显示排名,重复时合并名次(并列第…)
--ways 1
select * ,
(select count(distinct score)+1 from
SC B where B.C# = A.C# and B.score > A.score) 名次 from
SC A order by C#,名次
--ways 2
select *,DENSE_RANK()over(order by score desc)名次 from
SC where C# ='01' union all
select *,DENSE_RANK()over(order by score desc)名次 from
SC where C# ='02' union all
select *,DENSE_RANK()over(order by score desc)名次 from
SC where C# ='03'
4、按各科成绩(分科目)进行排序,并显示排名,Score重复时按学号升序排名,名次不重复
select * from
(select *,ROW_NUMBER()over(partition by C# order by score desc,S#)A
from SC)B
5、查询每门课程成绩最好的前两名
--ways 1
select * from SC B where (select count(score) from SC A where A.C# = B.C# and A.score > B.score ) <2
order by C#,score desc
--ways 2
select * from
(select *,ROW_NUMBER()over(partition by C# order by score desc,S#)名次 from SC)B
where B.名次<3