CREATE TABLE `test` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`stu_id` int(11) DEFAULT NULL,
`class_id` int(11) DEFAULT NULL,
`score` int(11) DEFAULT NULL,
`time` datetime DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=latin1;
查询某个同学某门课获得最高分数的时间:
SELECT
time
FROM
(
SELECT
stu_id AS stu_id,
class_id AS class_id,
max(score) AS score
FROM
student1
WHERE
stu_id = 1
AND class_id = 1
GROUP BY
stu_id,
class_id
) student2,
student1
WHERE
student1.stu_id = student2.stu_id
AND student1.class_id = student2.class_id
AND student1.score = student2.score