一道值得小心的SQL题目

一道值得小心的SQL题目

 

现有三个表Student（学生表）[ StuID 学生号，StuName 姓名 ]，
Subject（课程表）[ SubID 课程号，SubName 课程名 ],  
Score（成绩表）[ ScoreId 成绩记录号,SubID 课程号,StuID 学生号,Score 成绩 ]。
请用SQL实现：
姓名   英语   数学   语文  历史
张三   80     95     89    76
李四   90     72     84    96

 

create table Student
(
  StuID int identity(1001,1) primary key,
  StuName varchar(20)
)
insert into Student values('张三')
insert into Student values('李四')
create table Subject
(
  SubID int identity(1001,1) primary key,
  SubName varchar(20)
)
insert into Subject values('英语')
insert into Subject values('数学')
insert into Subject values('语文')
insert into Subject values('历史')
create table Score
(
  ScoreId int identity(1001,1) primary key,
  SubID int,
  StuId int,
  Score int
)
insert into Score values(1001,1001,80)
insert into Score values(1002,1001,95)
insert into Score values(1003,1001,89)
insert into Score values(1004,1001,76)
insert into Score values(1001,1002,90)
insert into Score values(1002,1002,72)
insert into Score values(1003,1002,84)
insert into Score values(1004,1002,96)
  select name,sum(ying)as 英语,sum(shu)as 数学,sum(yu)as 语文,sum(li) as 历史 from
   (select StuName as name ,(case when Score.SubID=1001 then Score else 0 end)as ying ,
   (case when Score.SubID=1002 then Score else  0 end)as shu ,
   (case when Score.SubID=1003 then Score else  0 end)as yu ,
   (case when Score.SubID=1004 then Score else  0 end)as li
     from Score,Student,Subject
    where Score.SubID=Subject.SubID and Score.StuId = Student.StuId) as tab
   group by name

姓名   英语   数学   语文  历史
张三   80     95     89    76
李四   90     72     84    96