查询所有同学的学生编号、学生姓名、选课总数、所有课程的成绩总和
联合查询不会显示没选课的学生:
select student.sid, student.sname,r.coursenumber,r.scoresum
from student,
(select sc.sid, sum(sc.score) as scoresum, count(sc.cid) as coursenumber from sc
group by sc.sid)r
where student.sid = r.sid;
如要显示没选课的学生(显示为NULL),需要使用join:
select s.sid, s.sname,r.coursenumber,r.scoresum
from (
(select student.sid,student.sname
from student
)s
left join
(select
sc.sid, sum(sc.score) as scoresum, count(sc.cid) as coursenumber
from sc
group by sc.sid
)r
on s.sid = r.sid
);
4.1 查有成绩的学生信息
这一题涉及到in和exists的用法,在这种小表中,两种方法的效率都差不多,但是请参考SQL查询中in和exists的区别分析
当表2的记录数量非常大的时候,选用exists比in要高效很多.
EXISTS用于检查子查询是否至少会返回一行数据,该子查询实际上并不返回任何数据,而是返回值True或False.
结论:IN()适合B表比A表数据小的情况
结论:EXISTS()适合B表比A表数据大的情况
select * from student
where exists (select sc.sid from sc where student.sid = sc.sid);
select * from student
where student.sid in (select sc.sid from sc);