CREATE TABLE `t_test` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`stuid` varchar(36) NOT NULL,
`score` int(11) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8;
INSERT INTO `testdb`.`t_test` (`id`, `stuid`, `score`) VALUES ('', '', '');
INSERT INTO `testdb`.`t_test` (`id`, `stuid`, `score`) VALUES ('', '', '');
INSERT INTO `testdb`.`t_test` (`id`, `stuid`, `score`) VALUES ('', '', '');
INSERT INTO `testdb`.`t_test` (`id`, `stuid`, `score`) VALUES ('', '', '');
INSERT INTO `testdb`.`t_test` (`id`, `stuid`, `score`) VALUES ('', '', '');
INSERT INTO `testdb`.`t_test` (`id`, `stuid`, `score`) VALUES ('', '', '');
INSERT INTO `testdb`.`t_test` (`id`, `stuid`, `score`) VALUES ('', '', '');
INSERT INTO `testdb`.`t_test` (`id`, `stuid`, `score`) VALUES ('', '', '');
INSERT INTO `testdb`.`t_test` (`id`, `stuid`, `score`) VALUES ('', '', '');
INSERT INTO `testdb`.`t_test` (`id`, `stuid`, `score`) VALUES ('', '', '');
测试数据
SELECT *,
(SELECT count(id) FROM t_test t2
where t1.stuid=t2.stuid AND
t2.score>t1.score -- 获取t2大于t1的记录数
)as maxcnt
from t_test t1
到这一步就很简单了.其实只要根据情况取macnt的数据就行了,如果要取前2条,那么筛选maxcnt<2的数据即可.
(如111学生大于99的有0条,大于90的有1条,大于34的有2条,那么要取最高分的前3条,就是maxcnt<3)
SELECT *
FROM t_test t1
WHERE
(
SELECT count(id) FROM t_test t2
where t2.stuid=t1.stuid and t2.score>t1.score
)<3
ORDER BY stuid asc,score DESC
效果图