27,579
社区成员
发帖
与我相关
我的任务
分享
--先是三表连接查询,这没什么复杂的,最正常的三表连接:
select a.sno,a.sname,c.cno,c.cname,b.grade
from student a inner join sc b on a.sno=b.sno
inner join course c on c.cno=b.cno
--条件:因为要找的是该课程的最高分,因此,条件是:
--不存在这样一种情况,即sc表中没有cno与当前连接查询中的那个cno相同,
--且成绩大于当前查询那条记录的成绩的
where not exists(select 1 from sc where cno=b.cno and grade>b.grade)
select a.sno,a.sname,b.cno,b.cname,c.grade
from student a,course b,sc c
where a.sno=c.sno and b.cno=c.cno and c.grade=
(
select max(grade)
from sc d
where d.cno=b.cno
)
select a.sno,a.sname,b.cno,b.cname,c.grade
from student a,course b,sc c
where c.grade=
(
select max(grade)
from sc
group by cno
)
select a.sno,a.sname,c.cno,c.cname,b.grade
from student a inner join sc b on a.sno=b.sno
inner join course c on c.cno=b.cno
where not exists(select 1 from sc where cno=b.cno and grade>b.grade)