SQL Server.第 三章

一，公示表表达式

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