常见SQL笔试题：学生表相关操作

题目：

CREATE TABLE `student`  (
  `id` int(0) NOT NULL,
  `name` varchar(50) NULL DEFAULT NULL,
  `age` int(0) NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE
);

CREATE TABLE `course`  (
  `id` int(0) NOT NULL,
  `name` varchar(50) NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE
);

CREATE TABLE `result`  (
  `id` int(0) NOT NULL,
  `sid` int(0) NULL DEFAULT NULL,
  `cid` int(0) NULL DEFAULT NULL,
  `score` varchar(50) NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE
);

1.查询大于10岁的学生
2.查询每个学生的每门课程的成绩
3.查询每个学生的最高分数的课程

答案：

1.查询大于10岁的学生

select * from student where age > 10;

2.查询每个学生的每门课程的成绩

select s.name as student_name,c.name as course_name,r.score
from student s
INNER JOIN result r
on s.id=r.sid
INNER JOIN course c
on r.cid=c.id;

3.查询每个学生的最高分数的课程

select s.id,s.name as student,c.name as max_course
from student s
INNER JOIN result r
on s.id=r.sid
INNER JOIN course c
on r.cid=c.id
where (r.sid,r.score)
in (select sid,MAX(score) from result GROUP BY sid);