有3张表,Student表、SC表和Course表
Student表:学号(Sno)、姓名(Sname)、性别(Ssex)、年龄(Sage)和系名(Sdept)
Course表:课程号(Cno)、课程名(Cname)和学分(Ccredit);
SC表:学号(Sno)、课程号(Cno)和成绩(Grade)
请使用SQL语句查询学生姓名及其课程总学分
(注:如果课程不及格,那么此课程学分为0)
Student表:学号(Sno)、姓名(Sname)、性别(Ssex)、年龄(Sage)和系名(Sdept)
Course表:课程号(Cno)、课程名(Cname)和学分(Ccredit);
SC表:学号(Sno)、课程号(Cno)和成绩(Grade)
请使用SQL语句查询学生姓名及其课程总学分
(注:如果课程不及格,那么此课程学分为0)
from student st,course c,sc s
where st.sno=s.sno and s.cno=c.cno) t group by t.sname
select s.sno,s.sname,sum(case when sc.grade>0 then c.ccredit else 0 end) totalcredit
from
student s left join sc on s.studentid = sc.studentid
left join course c on sc.courseid = c.courseid
group by s.sno,s.sname
from student s left join sc on s.studentid = sc.studentid
left join course c on sc.courseid = c.courseid
group by s.sno,s.sname
from Student a
left join SC b on a.Sno = b.Sno
join Course c on b.Cno = c.Cno
where b.Grade >= 60
group by a.Sno, a.Sname不用 left join 容易丢人,group by 没学号遇到重名就惨了。