请问这样的sql语句怎么写?

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

共3个回答

热心网友时间：2024-10-07 07:49

1)select A.mname as 姓名,B.score as 语文,C.score as 数学,D.score as 英语,E.score as 历史 from member A
left outer join (select mid,score from score where fid=(select fid from f where fname='语文')) B on A.mid=B.mid
left outer join (select mid,score from score where fid=(select fid from f where fname='数学')) C on A.mid=c.mid
left outer join (select mid,score from score where fid=(select fid from f where fname='英语')) D on A.mid=D.mid
left outer join (select mid,score from score where fid=(select fid from f where fname='历史')) E on A.mid=E.mid

2)select A.mname as 姓名,B.score as 语文,C.score as 数学,D.score as 英语,E.score as 历史 from member A
inner join (select mid,score from score where fid=(select fid from f where fname='语文') where score<70) B on A.mid=B.mid
inner join (select mid,score from score where fid=(select fid from f where fname='数学') where score<70) C on A.mid=c.mid
inner join (select mid,score from score where fid=(select fid from f where fname='英语') where score<70) D on A.mid=D.mid
inner join (select mid,score from score where fid=(select fid from f where fname='历史') where score<70) E on A.mid=E.mid

3)select A.mname,B.avgscore from member A
left outer join (select mid,avg(score) as avgscore from score) B on A.mid=B.mid order by avgscore desc

4)create procere Query @FName char(50),@Flag bit
as
if @Flag=0 --表示没有参加这门课程考试
select mid,mname from member where mid not in (select mid from score where fid=(select fid from f where fname=@FName))
else
select mid,mname from member where mid in (select mid from score where fid=(select fid from f where fname=@FName))

热心网友时间：2024-10-07 07:49

1.select * from member,f,score where member.mid=score.mid and f.fid=score.fid 各个表的键名最好都是唯一

其它的都差不多了吧，当然这种方法不是最好的实现方法

热心网友时间：2024-10-07 07:50

你这个用一句SQL语句有点麻烦,最好能做在程序里,这样比较好一点
语句嵌套一下