日期:2014-05-18 浏览次数:20409 次
create table StuScore ( ID int identity(1,1), [Name] varchar(20), Class varchar(20), Score int ) truncate table StuScore insert into StuScore values('张春明','语文',89) insert into StuScore values('张春明','数学',97) insert into StuScore values('刘菲','数学',97) insert into StuScore values('刘菲','英语',20) insert into StuScore values('刘菲','语文',70) insert into StuScore values('吴京','语文',91) insert into StuScore values('吴京','英语',96) insert into StuScore values('袁磊','数学',84) insert into StuScore values('袁磊','英语',96) select * from StuScore
select *,ROW_NUMBER()over( ORDER BY 總分 desc) AS 名次 from (select [Name],SUM(Score) AS 總分 FROM StuScore GROUP BY [Name])t select *,DENSE_RANK()over(ORDER BY 總分 desc) AS 名次 from (select [Name],SUM(Score) AS 總分 FROM StuScore GROUP BY [Name])t select *,RANK()over(ORDER BY 總分 desc) AS 名次 from (select [Name],SUM(Score) AS 總分 FROM StuScore GROUP BY [Name])t