sql三表查询

情景：

student  id    stname    sex

score   scoreid  stname  birth

course id    coursename  age

简单说明  a,b ,c 三表a.name=b.name a.id=c.id

现在要查 id ,name ,course 

有两种方法:

select a.id,b.name,c. coursename from student a,score b,course c where a.name=b.name and a.id=c.courseid; 
select a.id,b.name,c. coursename from student a inner join score b on a.name=b.name inner join course c on a.id=c.courseid;

 

 

变种：

student :id,sname,age

course:id,cname

grade:id,sid,cid,grade

 

 

请用一句sql筛选课程表id=1的学生姓名，科目名称，以及每个学生对应不同科目最高分数：

SELECT 
t.sname,
t.cname,
max(t.grade)
from
(SELECT
	s.id as sid,
	s.name as sname,
	g.grade,
	c.id as cid,
	c.name as cname
from
	student s
inner join grade g on
	g.sid = s.id
inner join course c on
	c.id = g.cid
)t
 where t.cid =1 
group by t.sname,t.cname