Question

写sql查询语句----高手请进

有3张表，Student表、SC表和Course表
Student表:学号（Sno）、姓名（Sname）、性别（Ssex）、年龄（Sage）和系名（Sdept）
Course表:课程号（Cno）、课程名（Cname）和学分（Ccredit）；
SC表：学号（Sno）、课程号（Cno）和成绩（Grade）
请使用SQL语句查询学生姓名及其课程总学分
（注：如果课程不及格，那么此课程学分为0）
现在很多学校执行学分制，只要拿到足够的学分，就能毕业。
学分和成绩的关系：比如《数据库原理》课程，规定有4个学分。只要你这门考试通过，就能拿到4个学分，否则拿不到，这门课程学分为0。
我这里就是要求查询：每个学生总共拿到多少学分
经过测试，回答者的SQL语句都不行，不过给了我灵感，下面是我测试过的正确语句：
方法1：select Sname,sum(Ccredit) as totalCredit from Student,Course,SC where Grade>=60 and Student.Sno=SC.Sno and Course.Cno=SC.Cno group by Sname
方法2：对xyphoenix的修改
select sname,sum(case when sc.grade<60 then 0 else course.Ccredit end) as totalCredit from Student,sc,course where sc.sno=student.sno and sc.cno=course.cno group by sname
方法3：对napolun180410的修改
select Sname,SUM(case when Grade<60 then 0 else Ccredit end) as totalGrade FROM SC JOIN Student ON(Student.sno = SC.sno) JOIN Course ON(SC.Cno = Course.Cno) GROUP BY Student.Sname;
本来想选——无满意答案，但各位热心帮忙，特别是napolun180410，在了解了学分和成绩的关系后，重写了代码，让我挺感动。为此，我改为投票。

Answer 1

if object_id('tempdb..#1') is not null drop table #1
SELECT S.Sno as 'Sno',
S.Sname as 'Sname',
S.Ssex as 'Sse',
S.Sage as 'Sage',
S.Sdept as 'Sdept',
SC.Cno as 'Cno',
SC.Grade as 'Grade',
C.Cname as 'Cname',
'Ccredit' = CASE WHEN SC.Grade >=60 then C.Ccredit ELSE 0 END
INTO #1
FROM Student S
LEFT JOIN SC SC ON SC.Sno = S.Sno
LEFT JOIN COURSE C ON C.Cno = SC.Cno

SELECT SELECT S.Sno as '学号',
S.Sname as '姓名',
S.Ssex as '性别',
S.Sage as '年龄',
S.Sdept as '系名',
SUM(S.Ccredit) as '学分'
FROM #1 S
GROUP BY S.Sno, S.Sname, S.Sex, S.Sage, S.Sdept

drop table #1

Answer 2

我懂了 这样就可以了 你试试

1.先将三张表按照 Sno 和 Cno 的对应关系连接起来
2. 使用CASE语句 当成绩及格(>60) 得到学分 否则为0
3. 按照学号（Sno) 分组.

select Student.Sno,Sname,SUM(case when Grade<60 then 0 else Ccredit end) as totalGrade FROM SC JOIN Student ON(Student.sno = SC.sno) JOIN Course ON(SC.sno = Course.sno) GROUP BY Student.Sno;

Answer 3

select Sname,SUM(case when Grade<60 then 0 else Grade end) as totalGrade FROM Student LEFT JOIN SC ON(Student.sno = SC.sno) GROUP BY Sname;

Answer 4

莫非lz的数据库原理教材也是丁宝康，董健全编写的那本？？

Answer 5

select sname,sum(case when sc.grade<60 then 0 else course.credit end) as totalCredit from student,sc,course where sc.sno=student.sno and sc.cno=course.cno and student.sno=123

其中123是学生某个的学号，由于sno是唯一确定一个学生的标识，所以用学号做查询条件。