交叉表查询---统计成绩（转）

 表1  StuInfo

如： StuID    Name     Class
　　 4020  　望仪容 　1班
 　　 4022  　答复 　　3班
 　　 4025  　地方 　　2班
 　　 4021  　达到 　　1班
 　　 4023  　的啊 　　3班
 　　 4024  　haoha 　　2班

表2  StuScore

如：StuID    Course    Score 
 　　 4020  　数学 　　 50
 　　 4020  　英语 　　 80
 　　 4022  　数学 　　 70
 　　 4022  　英语　　  80
 　　 4025  　数学 　　 30
 　　 4025  　英语 　　 50
 　　 4021  　数学　　  80
 　　 4021  　英语 　　 90
 　　 4023  　数学　　  60
 　　 4023  　英语 　　 70

一、查询每位学生姓名与总成绩。

SELECT  tbl.StuID  as  学号,Name  as  姓名,tbl.zcj  as  总分 
from  StuInfo  INNER   JOIN  
(
select  StuID, SUM (Score)  as  zcj  from  StuScore  group   by  StuID
)tbl 
on  StuInfo.StuID = tbl.StuID

结果：

         学号     　姓名 　　总分
１　 4020     　望仪容    　 130
２　 4021     　达到    　　 170
３　 4022     　答复    　　 150
４　 4023     　的啊   　　  130
５　 4025     　地方    　　 80

二、查询各班级的总分第一名。

我只查出了（班级、总分），但不知道怎样查出如下结果形式（班级、姓名、总分）

SELECT  Class  as  班级, MAX (score)  as  总分 
from
(
  select  A.StuID,A.Name,A.Class,B.Score  from  StuInfo A  INNER   JOIN  
    (
       select  StuID, Sum (score)  as  Score  from  StuScore  group   by  StuID
    )B 
  on  A.StuID = B.StuID
)Tmp  group   by  Class

结果：

        班级　　总分
1      1班　　 170
2      2班　　 80
3      3班　　 150

终于做出来了，不过感觉似乎有点麻烦！不知道有没简单点的？

SELECT  C.Class  as  班级,D.Name  as  姓名,C.MaxSco  as  总分 
FROM
(
select  Class, MAX (score)  as  MaxSco  from  
  ( select  A.StuID,A.Name,A.Class,B.Score 
     from  StuInfo A  INNER   JOIN  
        ( select  StuID, SUM (score)  as  Score  from  StuScore  group   by  StuID)B 
     on  A.StuID = B.StuID
   )D  group   by  Class
)C 
INNER   JOIN  
( select  A.StuID,A.Name,A.Class,B.Score 
   from  StuInfo A  INNER   JOIN  
     ( select  StuID, SUM (score)  as  Score  from  StuScore  group   by  StuID)B 
   on  A.StuID = B.StuID
)D 
ON  C.MaxSco = D.Score  and  C.Class = D.Class 
ORDER   BY  C.Class

 结果：

     　班级 　 姓名  　 总分
1   　1班  　     达到  　 170
2   　2班  　　地方  　   80
3   　3班  　　答复 　    150

转载于:https://www.cnblogs.com/wgx1323/archive/2007/06/12/780128.html