SQL Server.第三章-优快云博客

本文介绍SQL中使用WITH语句进行复杂查询的方法，包括如何计算学生总成绩及使用窗口函数ROW_NUMBER、RANK、DENSE_RANK进行成绩排名。此外，还展示了如何通过PARTITION BY实现按科目排名。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

一，公示表表达式

with sumscore(学号,总成绩)
as
(
   select stuid,SUM(score)
   from StuMarks
   group by stuid
)
select * from sumscore

with sscore(学号,姓名,总成绩)
as
(
   select StuInfo.stuid,stuname,SUM(score)
   from StuMarks,StuInfo
   where StuInfo.stuid=StuMarks.stuid
   group by StuInfo.stuid,stuname
)
select * from sscore

二，排序函数

ROW_NUMBER根据排序语句,递增排序
select ROW_NUMBER()over(order by AVG(score) desc ) as '名次',stuid,AVG(score) from StuMarks GROUP BY stuid
--rank根据排序语句,但是存在并列,跳空
select rank()over(order by AVG(score) desc ) as '名次',stuid,AVG(score) from StuMarks GROUP BY stuid
--dense_rank根据排序语句,但是存在并列,不跳空
select dense_rank()over(order by AVG(score) desc ) as '名次',stuid,AVG(score) from StuMarks GROUP BY stuid
--partition by分组列
select DENSE_RANK() over(partition by subject order by score desc)as'排名',StuInfo.stuid,stuName,[subject] from StuInfo,StuMarks where StuInfo.stuid=StuMarks.stuid

select DENSE_RANK() over(partition by subject order by score desc)as'排名',[subject],score from StuInfo,StuMarks where StuInfo.stuid = StuMarks.stuid