create talbe score (姓名 varchar(14),科目 varchar(14),分数 int)
insert into score select '张三','数学',85
union all select '张三','语文',90
union all select '张三','英语',88
union all select '李四','数学',87
union all select '李四','语文',86
union all select '李四','英语',92
union all select '王五','数学',90
union all select '王五','语文',78
union all select '王五','英语',88
select 姓名,sum( case when 科目='数学' then 分数 end ) as 数学,
sum( case when 科目='语文' then 分数 end ) as 语文,
sum( case when 科目='英语' then 分数 end ) as 英语,sum(分数) as 总分 into #s from score group by 姓名 order by 总分 desc
select *,(select count(distinct 总分) from #s where 总分>=a.总分) as 排名
from #s a
order by 总分 desc