直接3表关联就可以了,没那么复杂 select max(A.sname),sum(B.ccredit) from student A,course B,sc C WHERE A.SNO=C.SNO AND B.CNO=C.CNO AND C.GRADE>=60 group by A.sno order by A.sno
--拼数据 with student(sno,sname,ssex,sage)as( select 1,'张三','F',19 from dual union all select 2,'李四','F',22 from dual union all select 3,'李雷','M',21 from dual union all select 4,'莉莉','M',20 from dual union all select 5,'王五','F',23 from dual ), sc(sno,cno,grade)as( select 1,1,80 from dual union all select 1,2,79 from dual union all select 2,2,90 from dual union all select 3,1,39 from dual union all select 4,1,89 from dual ), course(cno,cname,ccredit)as( select 1,'语文',4 from dual union all select 2,'英语',3 from dual ) --查询语句 SELECT s.sname, SUM(CASE WHEN nvl(c.grade, 0) >= 60 THEN c2.ccredit ELSE 0 END) FROM student s LEFT JOIN sc c ON c.sno = s.sno LEFT JOIN course c2 ON c2.cno = c.cno GROUP BY s.sname
select a.sno, a.sname, sum(ccredit) from student a, sc b, course c where a.sno = b.sno and b.cno = c.cno group by a.sno, a.sname
select max(A.sname),sum(B.ccredit)
from student A,course B,sc C
WHERE A.SNO=C.SNO AND B.CNO=C.CNO
AND C.GRADE>=60
group by A.sno
order by A.sno
--拼数据
with student(sno,sname,ssex,sage)as(
select 1,'张三','F',19 from dual
union all
select 2,'李四','F',22 from dual
union all
select 3,'李雷','M',21 from dual
union all
select 4,'莉莉','M',20 from dual
union all
select 5,'王五','F',23 from dual
),
sc(sno,cno,grade)as(
select 1,1,80 from dual
union all
select 1,2,79 from dual
union all
select 2,2,90 from dual
union all
select 3,1,39 from dual
union all
select 4,1,89 from dual
),
course(cno,cname,ccredit)as(
select 1,'语文',4 from dual
union all
select 2,'英语',3 from dual
)
--查询语句
SELECT s.sname,
SUM(CASE
WHEN nvl(c.grade, 0) >= 60 THEN
c2.ccredit
ELSE
0
END)
FROM student s
LEFT JOIN sc c
ON c.sno = s.sno
LEFT JOIN course c2
ON c2.cno = c.cno
GROUP BY s.sname
from student a, sc b, course c
where a.sno = b.sno
and b.cno = c.cno
group by a.sno, a.sname