实现如下第一题：
我有如下方法可实现，但感觉太复杂了，求简便方法！
select mname as 姓名,isnull(yuwen,0) as 语文,isnull(shuxue,0) as 数学,isnull(yinyu,0) as 英语,isnull(lishi,0) as 历史
from score
--语文
left join
(select score.Fid,score.MID,Score as yuwen from score left join f on f.Fid = score.fid where fname = '语文') as yuwen
on yuwen.mid = score.mid
--数学
left join
(select score.Fid,score.MID,Score as shuxue from score left join f on f.Fid = score.fid where fname = '数学') as shuxue
on shuxue.mid = score.mid
--英语
left join
(select score.Fid,score.MID,Score as yinyu from score left join f on f.Fid = score.fid where fname = '英语') as yinyu
on yinyu.mid = score.mid
--历史
left join
(select score.Fid,score.MID,Score as lishi from score left join f on f.Fid = score.fid where fname = '英语') as lishi
on lishi.mid = score.mid
left join member on score.mid = member.mid
--分组
group by member.mname,isnull(yuwen,0),isnull(shuxue,0),isnull(yinyu,0),isnull(lishi,0)
select 姓名 = M.MName,
语文 = min(case
when F.FName = '语文' then convert(varchar(10),S.Score)
else '缺考'
end),
数学 = min(case
when F.FName = '数学' then convert(varchar(10),S.Score)
else '缺考'
end),
英语 = min(case
when F.FName = '英语' then convert(varchar(10),S.Score)
else '缺考'
end),
历史 = min(case
when F.FName = '历史' then convert(varchar(10),S.Score)
else '缺考'
end)
from Score as S inner join Course as F on (S.FID = F.FID)
inner join Member as M on (M.MID = S.MID)
group by M.MName
--查询考试同学中成绩低于70分的学员姓名和所对应的科目
select 姓名 = M.MName,科目 = F.FName,成绩 = S.Score
from Score as S inner join Course as F on (S.FID = F.FID)
inner join Member as M on (M.MID = S.MID) where S.Score < 70
--统计参加考试的学生的平均分并按平均分 降序排列
select 姓名 = M.MName,平均成绩 = sum(S.Score)/4
from Member as M inner join Score as S on (M.MID = S.MID)
group by M.MName
order by sum(S.Score)/4 desc
--创建存储过程 查询参加1，2，3，4及没有参加考试的学员的姓名和学号
create procedure usp_Search
@joinNum int = 4
as
--参见考试的数目不等于零的情况
if(@joinNum > 0)
begin
select 姓名 = M.MName,学号 = M.MID
from Member as M inner join Score as S on (M.MID = S.MID)
group by M.MName,M.MID
having count(S.MID) = @joinNum
end
else --未参加考试的学员姓名
begin
select 姓名 = M.MName,学号 = M.MID
from Member as M inner join Score as S
on M.MID not in (select MID from Score)
group by M.MName,M.MID
end
go
--调用存储过程 查询信息
EXEC usp_Search 2