情景:
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
![](https://img.laitimes.com/img/_0nNw4CM6IyYiwiM6ICdiwiI0gTMx81dsQWZ4lmZf1GLlpXazVmcvwFciV2dsQXYtJ3bm9CX9s2RkBnVHFmb1clWvB3MaVnRtp1XlBXe0xCMy81dvRWYoNHLwEzX5xCMx8FesU2cfdGLwMzX0xiRGZkRGZ0Xy9GbvNGLpZTY1EmMZVDUSFTU4VFRR9Fd4VGdsYTMfVmepNHLrJXYtJXZ0F2dvwVZnFWbp1zczV2YvJHctM3cv1Ce-cmbw5CM3ATO3U2YhFWZkFWMzkjMzYzX3ETMyETMwIzLclDMyIDMy8CXn9Gbi9CXzV2Zh1WavwVbvNmLvR3YxUjLyM3Lc9CX6MHc0RHaiojIsJye.png)
请用一句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