一个表,学生id,日期,分数,存了每个学生每天的分数,如何查出每个学生过去5天最高分数的记录
CREATE TABLE `A` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`sid` int(11) DEFAULT NULL,
`score` float DEFAULT NULL,
`date` date DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE `A` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`sid` int(11) DEFAULT NULL,
`score` float DEFAULT NULL,
`date` date DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
from A t
where `date`>curdate()-interval 5 day
and not exists (select 1 from A where `date`>curdate()-interval 5 day and sid=t.sid and score>t.score)
[征集]分组取最大N条记录方法征集,及散分....