SQL一题，分别用Oracle和SQLServer实现

[weiluo12]

有3张表，Student表、SC表和Course表
Student表:学号（Sno）、姓名（Sname）、性别（Ssex）、年龄（Sage）和系名（Sdept）
Course表:课程号（Cno）、课程名（Cname）和学分（Ccredit）；
SC表：学号（Sno）、课程号（Cno）和成绩（Grade）
请使用SQL语句查询学生姓名及其课程总学分
（注：如果课程不及格，那么此课程学分为0）

[jeansy911]

[Quote=引用 11 楼 xuejie09242 的回复:]
楼上，要查总学分，不需要课程名了。
SQL codeselect 学号=a.Sno,姓名=a.Sname,课程总学分=sum(casewhen b.Grade>=60then Ccreditelse0end)from Student aleftjoin SC bon a.Sno=b.Snoleftjoin Course con b.Cno=c.Cnogroupby a.Sno,a.Snameorderby a.Sno,a.Sname
[/Quote]
sno 学号。。

[zzxap]

select 学号=a.Sno,姓名=a.Sname,课程名=b.Cname,课程总学分=sum(case when b.Grade>=60 then Ccredit else 0 end)
from Student a
left join SC b on a.Sno=b.Sno
left join Course c on b.Cno=c.Cno
group by a.Sno,a.Sname,b.Cname
order by a.Sno,a.Sname,b.Cname

[上善若水_厚德载物]

if object_id('student') is not null 
  drop table student
create table student
(sno int,name varchar(10))
insert into student
select 1111,'张三'
union all
select 2222,'李四'

if object_id('course') is not null 
  drop table course
create table course
(cno int,ccredit int)
insert into course
select 2222,10
union all
select 3333,10


if object_id('sc') is not null 
  drop table sc
create table sc
(sno int,cno int,grade int)
insert into sc
select 1111,2222,59
union all
select 2222,2222,60
union all
select 1111,3333,60
union all
select 2222,3333,60

select * from student
select * from course
select * from sc

select st.name,sum(case when s.grade<60 then 0 else c.ccredit end) as 学分 from sc s,
course c,student st
where s.cno = c.cno and st.sno = s.sno 
group by st.name

/**
name       学分          
---------- ----------- 
李四         20
张三         10

（所影响的行数为 2 行）

**/

[ks_reny]

[Quote=引用楼主 weiluo12 的回复:]
有3张表，Student表、SC表和Course表
Student表:学号（Sno）、姓名（Sname）、性别（Ssex）、年龄（Sage）和系名（Sdept）
Course表:课程号（Cno）、课程名（Cname）和学分（Ccredit）；
SC表：学号（Sno）、课程号（Cno）和成绩（Grade）
请使用SQL语句查询学生姓名及其课程总学分
（注：如果课程不及格，那么此课程学分为0）

[/Quote]
SQL SERVER

 select s.Sname, 
        sum(case when sc.Grade<60 then 0 else c.Ccredit end) as 總學分 
 from  Student s,Course c,SC
 where s.sno=sc.sno and c.cno=sc.cno
 group by s.Sname

Oracle 上面的也可以再Oracle中執行,也可以用decode()函數

select s.Sname, 
        sum(decode(sign(sc.Grade-60),-1,0,c.Ccredit) as 總學分 
 from  Student s,Course c,SC
 where s.sno=sc.sno and c.cno=sc.cno
 group by s.Sname

[zhangwonderful]

oracle:
SELECT A.Sno,A.Sname,A.Sdept, SUM(CASE WHEN NVL(C.Ccredit)<60 THEN 0 ELSE C.Ccredit END) TTL_Ccredit FROM Student A LEFT JOIN SC B ON A.Sno=B.Sno LEFT JOIN Course C ON B.Cno=C.Cno GROUP BY A.Sno,A.Sname,A.Sdept

sql server:
SELECT A.Sno,A.Sname,A.Sdept, SUM(CASE WHEN ISNULL(C.Ccredit)<60 THEN 0 ELSE C.Ccredit END) TTL_Ccredit FROM Student A LEFT JOIN SC B ON A.Sno=B.Sno LEFT JOIN Course C ON B.Cno=C.Cno GROUP BY A.Sno,A.Sname,A.Sdept

[lihan6415151528]

SELECT SUM(CASE GRADE WHEN GRADE<60 THEN 0 END)[总学分] FROM  COURSE WHERE CNO IN(SELECT CNO FROM SC S LEFT JOIN STUDENT ST WHERE S.SNO = ST.SNO)

[sdhdy]

--SQL SERVER
select 姓名=a.Sname,课程名=b.Cname,课程总学分=sum(case when b.Grade>=60 then Ccredit else 0 end)  
from Student a
left join SC b on a.Sno=b.Sno
left join Course c on b.Cno=c.Cno

[sdhdy]

--modify
select 姓名=a.Sname,课程名=b.Cname,课程总学分=sum(case when b.Grade>=60 then Ccredit else 0 end)  
from Student a
left join SC b on a.Sno=b.Sno
left join Course c on b.Cno=c.Cno
group by a.Sname,b.Cname
order by a.Sname,b.Cname

[Quote=引用 2 楼 sdhdy 的回复:]
SQL code--SQL SERVERselect 姓名=a.Sname,课程名=b.Cname,课程总学分=sum(casewhen b.Grade>=60then Ccreditelse0end)from Student aleftjoin SC bon a.Sno=b.Snoleftjoin Course con b.Cno=c.Cno
[/Quote]

[feixianxxx]

[Quote=引用 2 楼 sdhdy 的回复:]
SQL code--SQL SERVERselect 姓名=a.Sname,课程名=b.Cname,课程总学分=sum(casewhen b.Grade>=60then Ccreditelse0end)from Student aleftjoin SC bon a.Sno=b.Snoleftjoin Course con b.Cno=c.Cno
[/Quote]
...

[jwdream2008]

select A.Sname
,sum(case when C.Grade<60 then 0 else B.Ccredit end) as '总学分'
From Student A inner join SC C on A.Sno=C.Sno
on Course B on C.Cno=B.Cno

[sdhdy]

--SQL SERVER
--为了避免姓名有重复的，应把学号也加上。
select 学号=a.Sno,姓名=a.Sname,课程名=b.Cname,课程总学分=sum(case when b.Grade>=60 then Ccredit else 0 end)  
from Student a
left join SC b on a.Sno=b.Sno
left join Course c on b.Cno=c.Cno
group by a.Sno,a.Sname,b.Cname
order by a.Sno,a.Sname,b.Cname

[guguda2008]

难度不大，就看谁最先贴出正确的了

[华夏小卒]

declare @Student table(Sno int ,Sname varchar(10),Ssex varchar(11),Sage int,Sdept varchar(11) )
declare @Course table(Cno int,Cname varchar(11), Ccredit int)
declare @sc table(Sno int ,Cno int ,Grade decimal(5,2))

insert @Student select 
1   ,     '张三'   ,  '男' ,   25 ,  '计算机系' union all select
2   ,     '李四'  ,   '女' ,   28 ,   '语文系' 
insert @Course select
3   ,    '计算机'   ,  2 union all select
5   ,    'C#'   ,  5    union all select
4   ,    '语文'   ,    1 
insert @sc select
1   ,    3   ,     127 union all select
1   ,    4   ,     59  union all select
1   ,    5   ,     80  union all select
2   ,    3   ,     39  union all select
2   ,    4   ,     80  
select t.*,d.[Ccredit] from @Student t 
join 
(select a.Sno,[Ccredit]=sum(case when c.Grade>60 then Ccredit else 0 end) from @Student a 
join @sc c
on a.Sno=c.Sno
join @Course b
on b.Cno=c.Cno
group by a.Sno)d
on t.Sno=d.Sno


Sno         Sname      Ssex        Sage        Sdept       Ccredit     
----------- ---------- ----------- ----------- ----------- ----------- 
1           张三         男           25          计算机系        7
2           李四         女           28          语文系         1

（所影响的行数为 2 行）

[jeansy911]

select a.sname,
sum(case  b.ccredit when c.grade>=60 then b.ccredit  else  0 end) 
from student a,course b,sc c 
where a.sno=c.sno and b.cno=c.cno 

[jeansy911]

create table student
(sno int)
insert into student
select 1111
union all
select 2222

create table course
(cno int,ccredit int)
insert into course
select 2222,10
union all
select 3333,10

create table sc
(sno int,cno int,grade int)
insert into sc
select 1111,2222,59
union all
select 2222,2222,60
union all
select 1111,3333,60
union all
select 2222,3333,60



select a.sno,
sum(case   when c.grade>=60 then b.ccredit  else  0 end)credit
from student a,course b,sc c 
where a.sno=c.sno and b.cno=c.cno 
group by a.sno

drop table student
drop table course
drop table sc
-------------------------
sno  credit
1111 10	
2222 20	

写详细了 捞分！！！

[xuejie09242]

楼上，要查总学分，不需要课程名了。

select 学号=a.Sno,姓名=a.Sname,课程总学分=sum(case when b.Grade>=60 then Ccredit else 0 end)  
from Student a
left join SC b on a.Sno=b.Sno
left join Course c on b.Cno=c.Cno
group by a.Sno,a.Sname
order by a.Sno,a.Sname