多次考试成绩一起排名

Question

下面是一次考试的成绩排名，如果是要将多次考试一起进行排名该如何做呢？也就是我想得到如下结果：
/*
姓名      数学排名      数学分数      语文排名      语文分数        政治排名        政治分数        总分          总分排名    考试名称
---------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- -----------
张三         2           90          3           88          1           90          268         1  期末考试
张三         2           90          3           88          1           90          268         1  期中考试
李四         1           92          1           90          NULL        NULL        182         2  期末考试
李四         1           92          1           90          NULL        NULL        182         2  期中考试
(4 行受影响)
*/
if object_id('[a]') is not null drop table a 
go 
create  table  A(姓名  varchar(10),科目  varchar(10),分数  int)  
  insert  a  select  '张三','语文',88  
  union  all  select  '张三','数学',90  
  union  all  select  '张三','政治',90  
  union  all  select  '李四','语文',90  
  union  all  select  '李四','数学',92  
  union  all  select  '王五','语文',70  
  union  all  select  '王五','数学',80  
  union  all  select  '刘二','语文',90  
  union  all  select  '刘二','政治',73  
      
  declare  @s  varchar(8000)  
  set  @s='select  姓名'  
  select  @s=@s+',['+科目+'排名]=sum(case  科目  when  '''+科目+'''  then  b.num  end)'  
               +',['+科目+'分数]=sum(case  科目  when  '''+科目+'''  then  b.分数  end)'    
  from  a  group  by  科目 
  exec(@s+',sum(b.分数) 总分,总分排名=(select  count(*)+1  from 
  (select  sum(分数)  as  总分  from  A  group  by  姓名)  s  where  总分>sum(b.分数)) 
  from 
  (select  t.*,(select  count(*)  from  a  where  科目=t.科目  and  分数>t.分数)+1  as  num  from  a  t)  b  
  group  by  姓名 order by 总分排名' )  
  Print(@S) 
  drop  table  A  
/*
姓名         数学排名        数学分数        语文排名        语文分数        政治排名        政治分数        总分          总分排名
---------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- -----------
张三         2           90          3           88          1           90          268         1
李四         1           92          1           90          NULL        NULL        182         2
刘二         NULL        NULL        1           90          2           73          163         3
王五         3           80          4           70          NULL        NULL        150         4(4 行受影响)
*/

Answer 1

表中没有期中和期末?怎么知道考试是期中还是期末呢?如果表中有记录,就group by 一下吧~

Answer 2

select  @s=@s+',['+科目+'排名]=sum(case  科目  when  '''+科目+'''  then  b.num  end)'  
               +',['+科目+'分数]=sum(case  科目  when  '''+科目+'''  then  b.分数  end)'    
  from  a  group  by  科目 
这要是这里我加有sum呀，多次成绩一起就把各次考试成绩只要是同一科目的都累加起来了

Answer 3

if object_id('[a]') is not null drop table a 
go 
create  table  A(姓名  varchar(10),科目  varchar(10),分数  int,考试名称 varchar(50))  
  insert  a  select  '张三','语文',88,'期中考试'
  union  all  select  '张三','数学',90 ,'期中考试' 
  union  all  select  '张三','政治',90 ,'期中考试' 
  union  all  select  '李四','语文',90 ,'期中考试' 
  union  all  select  '李四','数学',92 ,'期中考试' 
  union  all  select  '王五','语文',70 ,'期中考试' 
  union  all  select  '王五','数学',80,'期中考试'  
  union  all  select  '刘二','语文',90  ,'期中考试'
  union  all  select  '刘二','政治',73 ,'期中考试'union  all  select  '张三','数学',94 ,'期末考试' 
  union  all  select  '张三','政治',91 ,'期末考试' 
  union  all  select  '李四','语文',98 ,'期末考试' 
  union  all  select  '李四','数学',90 ,'期末考试' 
  union  all  select  '王五','语文',80 ,'期末考试' 
  union  all  select  '王五','数学',70,'期末考试'  
  union  all  select  '刘二','语文',91  ,'期末考试'
  union  all  select  '刘二','政治',72 ,'期末考试'
 union  all  select  '张三','语文',82 ,'期末考试'
select  t.*,(select  count(*)  from  a  where  科目=t.科目 and 考试名称=t.考试名称  and  分数>t.分数)+1  as  num  from  a  t
我这样可以得到如下数据：
姓名     科目     成绩     名称     名次
张三 语文 88 期中考试 3
张三 数学 90 期中考试 2
张三 政治 90 期中考试 1
李四 语文 90 期中考试 1
李四 数学 92 期中考试 1
王五 语文 70 期中考试 4
王五 数学 80 期中考试 3
刘二 语文 90 期中考试 1
刘二 政治 73 期中考试 2
张三 数学 94 期末考试 1
张三 政治 91 期末考试 1
李四 语文 98 期末考试 1
李四 数学 90 期末考试 2
王五 语文 80 期末考试 4
王五 数学 70 期末考试 3
刘二 语文 91 期末考试 2
刘二 政治 72 期末考试 2
张三 语文 82 期末考试 3
如何把这些数据行转列并进行总分排名呢，就是得到这样的格式：
名称    姓名   语文 语文排名 数学 数学排名  政治 政治排名 总分 总分排名  

Answer 4

if object_id('[a]') is not null drop table a
go
create  table  A(姓名  varchar(10),科目  varchar(10),分数  int,考试名称 varchar(50)) 
  insert  a  select  '张三','语文',88,'期中考试'
  union  all  select  '张三','数学',90 ,'期中考试'
  union  all  select  '张三','政治',90 ,'期中考试'
  union  all  select  '李四','语文',90 ,'期中考试'
  union  all  select  '李四','数学',92 ,'期中考试'
  union  all  select  '王五','语文',70 ,'期中考试'
  union  all  select  '王五','数学',80,'期中考试' 
  union  all  select  '刘二','语文',90  ,'期中考试'
  union  all  select  '刘二','政治',73 ,'期中考试'union  all  select  '张三','数学',94 ,'期末考试'
  union  all  select  '张三','政治',91 ,'期末考试'
  union  all  select  '李四','语文',98 ,'期末考试'
  union  all  select  '李四','数学',90 ,'期末考试'
  union  all  select  '王五','语文',80 ,'期末考试'
  union  all  select  '王五','数学',70,'期末考试' 
  union  all  select  '刘二','语文',91  ,'期末考试'
  union  all  select  '刘二','政治',72 ,'期末考试'
union  all  select  '张三','语文',82 ,'期末考试'   declare  @s  varchar(8000)  
  set  @s='select  姓名,考试名称'  
  select  @s=@s+',['+科目+'排名]=sum(case  科目  when  '''+科目+'''  then  b.num  end)'  
               +',['+科目+'分数]=sum(case  科目  when  '''+科目+'''  then  b.分数  end)'    
  from  a  group  by  科目 
  exec(@s+',sum(b.分数) 总分,总分排名=(select  count(*)+1  from 
  (select  sum(分数)  as  总分  from  A  group  by  姓名)  s  where  总分>sum(b.分数)) 
  from 
  (select  t.*,(select  count(*)  from  a  where  科目=t.科目 and 考试名称=t.考试名称  and  分数>t.分数)+1  as  num  from  a  t)  b  
  group  by  姓名,考试名称 order by 总分排名' )  
  Print(@S) 
  drop  table  A  /*
李四 期末考试 2 90 1 98 NULL NULL 188 5
刘二 期末考试 NULL NULL 2 91 2 72 163 5
王五 期末考试 3 70 4 80 NULL NULL 150 5
张三 期末考试 1 94 3 82 1 91 267 5
李四 期中考试 1 92 1 90 NULL NULL 182 5
刘二 期中考试 NULL NULL 1 90 2 73 163 5
王五 期中考试 3 80 4 70 NULL NULL 150 5
张三 期中考试 2 90 3 88 1 90 268 5
*/

Answer 5

set  @s='select  姓名,考试名称'  
  select  @s=@s+',['+科目+'排名]=sum(case  科目  when  '''+科目+'''  then  b.num  end)'  
               +',['+科目+'分数]=sum(case  科目  when  '''+科目+'''  then  b.分数  end)'    
  from  a  group  by  科目 
  exec(@s+',sum(b.分数) 总分,总分排名=(select  count(*)+1  from 
  (select  sum(分数)  as  总分  from  A  group  by  姓名)  s  where  总分>sum(b.分数)) 
  from 
  (select  t.*,(select  count(*)  from  a  where  科目=t.科目 and 考试名称=t.考试名称  and  分数>t.分数)+1  as  num  from  a  t)  b  
  group  by  姓名,考试名称 order by 总分排名' )
太牛了,能给解说一下上面的吗

Answer 6

我想了解一下,这种语句之间是如何划分的如
select  @s=@s+',['+科目+'排名]=sum(case  科目  when  '''+科目+'''  then  b.num  end)'  
              +',['+科目+'分数]=sum(case  科目  when  '''+科目+'''  then  b.分数  end)'     from  a  group  by  科目
中的select 是否与from a group by 科目匹配

Answer 7

请教wybwml2008行列转换有基本的语句吗,因为每次行列转换时,我只能依葫芦画瓢,不能够实现灵活运用

Answer 8

结贴，弄对了，少加了个  s  where  s.总分>sum(b.分数) and s.考试名称=b.考试名称)