SQL行列转换中的特殊问题

Question

有表A
学生姓名 课程名称 学生成绩
夏 数学 91
夏 语文 52
夏 英语 93
夏 化学 54
张 数学 81
张 语文 51
张 英语 83
张 化学 84
郭 数学 81
郭 语文 52
郭 英语 53
郭 化学 84
想得到以下输出：
学生姓名      最高成绩 次高成绩 第三高成绩 最低成绩
夏       
张
郭

Answer 1

select 学生姓名, 最高成绩=max(case when no=1 then 学生成绩 end),
最高成绩=max(case when no=3 then 学生成绩 end),
最高成绩=max(case when no=2 then 学生成绩 end),
最高成绩=max(case when no=4 then 学生成绩 end)
from (select row_number() over(partition by 学生姓名 order by 学生成绩 desc) no,*
      from tb)a
group by 学生姓名

Answer 2

select 学生姓名, 最高成绩=max(case when no=1 then 学生成绩 end),
次高成绩=max(case when no=2 then 学生成绩 end),
第三高成绩=max(case when no=3 then 学生成绩 end),
最低成绩=max(case when no=4 then 学生成绩 end)
from (select row_number() over(partition by 学生姓名 order by 学生成绩 desc) no,*
      from tb)a
group by 学生姓名

Answer 3

--sql 2000用子查询完成。select 学生姓名 ,
       max(case px when 1 then 学生成绩 else 0 end) 最高成绩,
       max(case px when 2 then 学生成绩 else 0 end) 次高成绩,
       max(case px when 3 then 学生成绩 else 0 end) 第三高成绩,
       max(case px when 4 then 学生成绩 else 0 end) 最低成绩
from
(
  select t.* , px = (select count(1) from a where 学生姓名 = t.学生姓名 and 学生成绩 > t.学生成绩) + 1 from a t
) m
group by 学生姓名
--sql 2005用row_number完成。select 学生姓名 ,
       max(case px when 1 then 学生成绩 else 0 end) 最高成绩,
       max(case px when 2 then 学生成绩 else 0 end) 次高成绩,
       max(case px when 3 then 学生成绩 else 0 end) 第三高成绩,
       max(case px when 4 then 学生成绩 else 0 end) 最低成绩
from
(
  select t.* , px = row_number() over(partition by 学生姓名 order by 学生成绩 desc) from a t
) m
group by 学生姓名以上做法均不考虑同一个人其成绩出现并列情况.

Answer 4

TO ssp2009
我用的SQL2000 报错 
服务器: 消息 195，级别 15，状态 10，行 5
'row_number' 不是可以识别的 函数名。

Answer 5

to dawugui
谢谢哈！能正确执行！

Answer 6

select sn=identity(int,1,1), * into #temp from t order by 学生成绩select 学生姓名 ,
       max(case px when 1 then 学生成绩 else 0 end) 最高成绩,
       max(case px when 2 then 学生成绩 else 0 end) 次高成绩,
       max(case px when 3 then 学生成绩 else 0 end) 第三高成绩,
       max(case px when 4 then 学生成绩 else 0 end) 最低成绩
from
(
  select t.* , px = (select count(1) from #temp where 学生姓名 = t.学生姓名 and sn > t.sn) + 1 from #temp t
) m
group by 学生姓名

Answer 7

 to  andy_liucj 能完全正确执行，谢谢哈！