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

2024-12-26 15:58:41

推荐回答（5个）

回答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

回答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;

回答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;

回答4：

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

回答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是唯一确定一个学生的标识，所以用学号做查询条件。