前几天遇到一个MySQL面试题

Question

学生成绩表

CREATE TABLE `score` (
  `score_id` INT NOT NULL AUTO_INCREMENT COMMENT '分数id',
  `exam_time` DATETIME NOT NULL COMMENT '考试时间',
  `make_up` INT NOT NULL DEFAULT '0' COMMENT '是否补考',
  `course_num` INT NOT NULL COMMENT '课程id',
  `student_num` INT NOT NULL COMMENT '学生id',
  `score` INT NOT NULL COMMENT '分数',
  PRIMARY KEY (`score_id`),
  KEY `course_num` (`course_num`),
  KEY `student_num` (`student_num`),
  CONSTRAINT `score_ibfk_1` FOREIGN KEY (`course_num`) REFERENCES `course` (`course_num`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `score_ibfk_2` FOREIGN KEY (`student_num`) REFERENCES `student` (`student_num`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=INNODB AUTO_INCREMENT=21 DEFAULT CHARSET=latin1 COMMENT='学生成绩表'

课程表

CREATE TABLE `course` (
  `course_num` INT NOT NULL COMMENT '课程号',
  `course_name` VARCHAR(100) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '课程名',
  `course_hour` INT NOT NULL COMMENT '课程学时',
  `course_score` VARCHAR(10) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '课程学分',
  PRIMARY KEY (`course_num`),
  KEY `course_num` (`course_num`)
) ENGINE=INNODB DEFAULT CHARSET=latin1 COMMENT='课程信息表'

学生表

CREATE TABLE `student` (
  `student_num` INT NOT NULL COMMENT '学生学号',
  `student_name` VARCHAR(5) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '学生姓名',
  `student_sex` VARCHAR(1) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT '男' COMMENT '学生性别',
  `student_birthday` VARCHAR(20) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '学生生日',
  PRIMARY KEY (`student_num`),
  KEY `student_num` (`student_num`)
) ENGINE=INNODB DEFAULT CHARSET=latin1 COMMENT='学生基本信息表'

现在是想获取以下表，获取四个学生的学生id、姓名、课程编号、最新成绩

想问一下大家怎么写查询语句，谢谢大家了

Answer 1

mysql 8.0以上支持开窗函数,可以使用row_number进行分组排序

select t.student_num,t.student_name,t.course_name,t.score from (
select s.student_num,s.student_name,c.course_name,sc.score,
row_number() over (partition by s.student_num,c.course_num order by exam_time desc) rn
 from score sc,course c,student s 
where sc.course_num=c.course_num and sc.student_num=s.student_num
) t where rn=1

由于你没说是否可能存在缺考的情况，我就没左关联了