S2 SQL测试题

发布网友发布时间：2024-09-30 06:47

共1个回答

热心网友时间：2024-11-23 22:10

第一题给你做完了.
select f.mname,
f.语文,
f.数学,
f.英语,
f.历史 from
(select MName
,
语文=sum(case when FID='F001' then score end),
数学=sum(case when FID='F002' then score end),
英语=sum(case when FID='F003' then score end),
历史=sum(case when FID='F004' then score end)
from dbo.Member inner join dbo.score on dbo.Member.MID=dbo.score.MID
group by MName
) f
where f.数学 is not null and f.语文 is not null and f.英语 is not null and f.历史 is not null
第二题
select MName
,
语文=sum(case when FID='F001' then score end),
数学=sum(case when FID='F002' then score end),
英语=sum(case when FID='F003' then score end),
历史=sum(case when FID='F004' then score end)
from dbo.Member inner join dbo.score on dbo.Member.MID=dbo.score.MID
where score<70
group by MName
第三题
select 学生姓名=f.mname,
f.语文,
f.数学,
f.英语,
f.历史,
f.平均分
from
(
select MName
,
语文=sum(case when FID='F001' then score end),
数学=sum(case when FID='F002' then score end),
英语=sum(case when FID='F003' then score end),
历史=sum(case when FID='F004' then score end),
平均分=avg(score)
from dbo.Member inner join dbo.score on dbo.Member.MID=dbo.score.MID
group by MName
) f
order by f.平均分 desc

最后一个查出1,2,3,4门没参加考试和没参加考试的人的学号和姓名~题出的不太明确我不知道他要什么效果

存储过程你会写吧查询语句给你~是不是你要的效果我不清楚

print '没参加语文考试的学生有:'
select 学生姓名=f.MName,f.学号
from
(
select MName
,
语文=sum(case when FID='F001' then score end),
数学=sum(case when FID='F002' then score end),
英语=sum(case when FID='F003' then score end),
历史=sum(case when FID='F004' then score end),
学号=Member.MID
from dbo.Member inner join dbo.score on dbo.Member.MID=dbo.score.MID
group by MName,Member.MID
) f
where f.语文 is null

自己该一下where条件你要是想查参加的就where f.语文not in null